How can I strip Leading zero's from a string?

Question

Post reply

How can I strip Leading zero's from a string?

tschuler-738392

Ten Centuries

Points: 1172
More actions
June 12, 2014 at 11:16 am

#286645

I need some help stripping leading 0's from a string. The column name in the table is code. Here are some examples.
00345.0001
02345.0123
I need to be able to strip the leading zero's from the first part before the . and the second part after the . so that they return the following values.
345.1
2345.123
I have tried the following but it only strips the 0 from the beginning of the string.
select SUBSTRING(code, PATINDEX('%[^0]%', code+'.'), LEN(code))
from accountmain
where 1=1
and statusid = 1
Thank you in advance for your help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Maybe something like this could work?

WITH SampleData AS(

SELECT '00345.0001' code UNION ALL

SELECT '02345.0123' code UNION ALL

SELECT '025013'

)

SELECT ISNULL( CAST( CAST( PARSENAME(code, 2) AS INT) AS varchar( 10)) + '.', '')

+ CAST( CAST( PARSENAME(code, 1) AS INT) AS varchar( 10))

FROM SampleData

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tschuler-738392 Ten Centuries Points: 1172 More actions · Answer 2

It works if I type in the values I need changing but when I modify your query to run against the accountmain table using the code field it returns me an error. Can't convert a nvarchar value to int.

WITH SampleData AS(

SELECT code from accountmain

)

SELECT ISNULL( CAST( CAST( PARSENAME(code, 2) AS INT) AS varchar( 10)) + '.', '')

+ CAST( CAST( PARSENAME(code, 1) AS INT) AS varchar( 10))

FROM SampleData

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the nvarchar value '0HOLD' to data type int.

I have some entries in that table that are not numbers. I need to run it against the whole table.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Fair enough. I thought you only had digits.

Check for the following. 😉

WITH SampleData AS(

SELECT '00345.0001' code UNION ALL

SELECT '02345.0123' code UNION ALL

SELECT '025013' code UNION ALL

SELECT '0HOLD.0250'

)

SELECT code,

ISNULL( SUBSTRING(Lcode, PATINDEX('%[^0]%', Lcode), 50) + '.', '')

+ SUBSTRING(Rcode, PATINDEX('%[^0]%', Rcode), 50)

FROM SampleData

CROSS APPLY (SELECT PARSENAME(code, 2) AS Lcode, PARSENAME(code, 1) AS rcode) x

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tschuler-738392 Ten Centuries Points: 1172 More actions · Answer 4

Thank you. That work like a charm. I appreciate the response.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Thank you for the feedback. You had the right idea on how to do it, I just helped you to get all the way there. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2