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

Question

Post reply

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

Viewing 5 posts - 1 through 6 (of 6 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