Remove first leading 0 from varchar with MSSQL

Question

How I can remove only the first 0 character from a varchar?

For example '000303' ==> '00303'

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

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

Thanks

Answer 1

Try using 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 you can also use

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

'000303' LIKE '0%' (ughai suggestion)

Answer 2

Try this:

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

This will remove the first character from the varchar column.

If it is specific to 0 then try this:

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