CODEWITHSUNDEEP
sql-servert-sql
Maxime
Maxime

Reputation: 467

Remove first leading 0 from varchar with MSSQL

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

Upvotes: 5

Views: 128

Answers (3)

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

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

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

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)

Upvotes: 6

Michael Dunlap
Michael Dunlap

Reputation: 4310

Something like

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

Upvotes: 0

Related Questions