CODEWITHSUNDEEP
sqlsql-serverfunctionstored-procedures
JVGBI
JVGBI

Reputation: 575

SQL Server : function to trim only leading zero's

I have to make a function that trims leading zero's. It's however a little more complex than just getting rid of the first zero's.

Few examples and results:

I have built a query myself and came up with the following

 RETURN LEFT(@input, PATINDEX('%0%', @input)-1) + 
 SUBSTRING(SUBSTRING(@input, PATINDEX('%0%',@input), LEN(@input)), PATINDEX('%[^0]%', SUBSTRING(@input,PATINDEX('%0%',@input),LEN(@input))),len(@input))

Now, this works really good to get the above results.

However:

ZBET108 should stay ZBET108. - the above query returns ZBET18

I can't get it to work. The query should find the first numeric string and trim the leading zero's of that. What it seems to do now is search for the first zero and remove it.

Could anyone assist me in this?

Thanks a lot guys.

Upvotes: 2

Views: 229

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270723

I think this might work:

RETURN (CASE WHEN LEFT(@input, CHARINDEX('0', @input)-1) LIKE '%[0-9]%'
             THEN @input
             ELSE LEFT(@input, CHARINDEX('0', @input)-1) + 
 SUBSTRING(SUBSTRING(@input, PATINDEX('%0%',@input), LEN(@input)), PATINDEX('%[^0]%', SUBSTRING(@input,PATINDEX('%0%',@input),LEN(@input))),len(@input))
        END)

This just checks that the first 0 is the first digit in the string.

Upvotes: 1

Related Questions