Remove Leading Zeros in any situation T-SQL

Question

Post reply

Remove Leading Zeros in any situation T-SQL

cusvenus

Mr or Mrs. 500

Points: 525
More actions
December 26, 2022 at 5:21 pm

#4130384

Comments posted to this topic are about the item Remove Leading Zeros in any situation T-SQL

Viewing 4 posts - 1 through 3 (of 3 total)

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

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 1

Unfortunately, if the text includes 0, this method will fail.

😎

declare @String varchar(30)
select @String = 'X01TEST00001234'
select 
case 
when ISNUMERIC(@String) = 0 then SUBSTRING (@String,0,CHARINDEX('0',@String)) + SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) 
else SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) 
end

will return

X1TEST00001234

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 2

@ Venugopal Saride,

I have to say that is IS a very interesting way to try to do this but I have to agree with Eirikur... It doesn't meet the promises of "any situation" or "all leading zeros". It also doesn't allow for the actual value of zero even if there are no leading zeros for that. Example follows:

declare @String varchar(30)
select @String = 'Test0'
select 
case 
when ISNUMERIC(@String) = 0 
then SUBSTRING (@String,0,CHARINDEX('0',@String)) + SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) 
else SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) 
end

Result:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16167 More actions · Answer 3

neither does it deal with scientific values where the whole string IS the value

declare @String varchar(30)
select @String = '1E02'
select 
 try_convert(float, @string)
,case 
when ISNUMERIC(@String) = 0 then SUBSTRING (@String,0,CHARINDEX('0',@String)) + SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) 
else SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) 
end