Remove first leading 0 from varchar with MSSQL

Question

How can I remove only the first character from varchar?

For example, '000303' ==> '00303'

I tried this without success, all 0 characters are removed:

SELECT SUBSTRING('000303', PATINDEX('%[^0]%', '000303'),LEN('000303')); 

thanks

Answer 1

Try using the STUFF Function

 SELECT CASE WHEN LEFT('000303',1) = '0' THEN STUFF('000303',1,1,'') ELSE '000303' END 

or Use RIGHT Function

 SELECT CASE WHEN LEFT('000303',1) = '0' THEN RIGHT('000303', LEN('000303')-1) ELSE '000303' END 

Instead of LEFT('000303',1) = '0' check that you can also use

charindex('0','000303') = 1 or

'000303' LIKE '0%' (offer from Shanghai)

Answer 2

Try the following:

 SELECT RIGHT(MyColumn, LEN(MyColumn) - 1) 

This will remove the first character from the varchar column.

If this value is 0, try the following:

 SELECT CASE WHEN LEFT(MyColumn,1) = '0' THEN RIGHT(MyColumn, LEN(MyColumn) - 1) ELSE MyColumn END 

Answer 3

Sort of

 SELECT CASE WHEN LEFT(Mycol,1) = '0' THEN SUBSTRING(MyCOL, 2, LEN(MyCOL)) END