Add Leading Zero

SELECT RIGHT('000000' + '8896', 6) AS str_new

Remove zeros

---//Remove trailing zeros from decimal
SELECT CAST(123.45700 as DECIMAL(9,6)),   
       CAST(CAST(123.45700 as DECIMAL(9,6)) as float)

Removing First orLast Character

declare @name varchar(20) ='rita,'
Select substring(@name, 2, len(@name)-1) as AfterRemoveFirstCharacter  
Select left(@name, len(@name)-1) as AfterRemoveLastCharacter  

---Using Condition
SELECT Result = CASE WHEN RIGHT(@name, 1) IN (',') 
                THEN left(@name, len(@name)-1) 
				ELSE 'No Comma found' END

--- Example 1
select reg.* from employee_attendance_register_t reg
where convert(@Att_date,103)=convert(datetime,reg.date,103)
	and CAST(left(reg.holiday_ot ,2) as int )=8 --removing char & convert to int
	
--- Example 2				
select right(CONVERT(VARCHAR(5),CONVERT(DATETIME, reg.OfficeTimeIn, 0), 108),7) as in_time
employee_attendance_register_t reg 
where convert(datetime,reg.date,103)='2020-10-17'

------Removing anything by replace
---- SELECT REPLACE(FieldName, 'FromKeyWord','ToKeyWord') 
update r set r.RecommendationOfInspector = REPLACE(r.RecommendationOfInspector , n.Serial+')', '')

from RecommendationOfInspectorToAvoidAccident_T r 
inner join PredefinedRecommandationViolationNote_T n on  n.SubjectID = r.SubjectOrLicenseID
where   r.RecommendationType ='security' 
and r.SubjectOrLicenseID >0 

SUBSTRING in sql

---- SUBSTRING(string, start, length)

--Remove the last character from a string
select substring('test string', 1, (len('test string') - 1))

--Example1: cut string based on specific char.
declare @EmpIDs varchar(50)
set @EmpIDs ='113958#313'

select LEFT(@EmpIDs, CHARINDEX('#', @EmpIDs + '#') -1),RIGHT(@EmpIDs, Charindex('#', Reverse(@EmpIDs)) - 1)

--Example 2:
declare @empIDS varchar(500)='113958#3$13,114652#15$3,121083#51$23,28567#9$33,34708#17$22,37999#64$8,' 

SELECT val AS EmpID	INTO #Temp from split(@empIDS,',')
DELETE FROM #Temp WHERE EmpID=''

select LEFT(EmpID, CHARINDEX('#', EmpID) -1) AS First,RIGHT(EmpID, Charindex('$', Reverse(EmpID)) - 1) As Last,
SUBSTRING(EmpID, charindex('#', EmpID)+1, charindex('$', EmpID) - charindex('#', EmpID)-1) As Middle
from #Temp

DROP TABLE #Temp

--output:
-----------------------------
First   	 Last    	Middle
113958	    13	      3
114652	    3	       15
121083	    23	     51
28567	      33	     9
34708	      22	     17
37999	      8	       64
-----------------------------