Remove Leading Zeros

Question

Post reply

Remove Leading Zeros

dba_user

Default port

Points: 1458
More actions
August 28, 2003 at 11:11 am

#81093

I have to create a view of a table.
In the table, there is a column with leading zeros such as (00005,00023,00001,....)
In my view, I am going to change that column to remove the leading zeros (5,23,1,...)
How can I do that in T-SQL??
Would I have to create a cursor that goes through each record in the column and changes it and then how would I create it in the view??
Thanks for your help!!

Viewing 14 posts - 1 through 13 (of 13 total)

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

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

Try using the replace something like:

declare @lz varchar(10)

set @lz = '000001'

select @lz

select replace(@lz,'0','')

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

bp SSCarpal Tunnel Points: 4656 More actions · Answer 2

assuming if all values in your column is numeric in value only, then you can cast that column as an integer data type. See below for example:

SET NOCOUNT ON

DECLARE @tt TABLE(THE_VALUE VARCHAR(100))

INSERT INTO @tt VALUES('00100');

INSERT INTO @tt VALUES('00010');

INSERT INTO @tt VALUES('00001');

SELECT CAST(THE_VALUE AS INT) FROM @tt

/* you get:

100

10

1

*/

BrenBart SSCrazy Points: 2622 More actions · Answer 3

There's probably a much simpler way to do this...

create table RemLZ (LZ varchar(25))

insert into RemLZ values ('000234')

insert into RemLZ values ('000203040')

insert into RemLZ values ('000')

insert into RemLZ values ('234000')

insert into RemLZ values ('00000200034000')

SELECT

LZ,

case

when patindex('%[^0]%', LZ)= 0

then '0'

else

right(LZ,

(len(LZ)- PATINDEX('%[^0]%',LZ)+ 1))

end

from RemLZ

"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)

BrenBart SSCrazy Points: 2622 More actions · Answer 4

Sorry, ugly spacing.

I've always thought SQL should have a simple function like this:

TRIMSTRING(<string to be searched>, <pattern to be removed>, <Remove from R or L>)

"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 5

Gee I don't know what I was thinking. A sole replace is not going to do it. But multiple replaces with a ltrim will:

replace(ltrim(replace(<value with leading zeroes>,'0',' ')),' ',0)

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Hi Jill,

quote:
I have to create a view of a table.
In the table, there is a column with leading zeros such as (00005,00023,00001,....)
In my view, I am going to change that column to remove the leading zeros (5,23,1,...)

any reasons not to change the underlying data type in the table?

quote:
How can I do that in T-SQL??

bp's solution to CAST as Integer is probably the easiest one

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

satishbt SSC Eights! Points: 828 More actions · Answer 7

write an UPDATE statement, and us CONVERT or CAST and convert the datatype of that Column in the UPDATE statement, then the leading zeros will be removed.

satishbt SSC Eights! Points: 828 More actions · Answer 8

Sorry, forgot to mention, while using the CONVERT of CAST in your UPDATE statement for that Column, you should CONVERT the Column to INT.

coondapoor Valued Member Points: 68 More actions · Answer 9

declare@var1varchar(10)

SELECT@var1 = '1105231'

selectSUBSTRinG(@var1,PATINDEX('%[1-9]%',@var1),LEN(@var1)-PATINDEX('%[1-9]%',@var1)+1)

bp SSCarpal Tunnel Points: 4656 More actions · Answer 10

Very nice, BrenBart and coondapoor!

Edited by - bp on 08/29/2003 12:52:37 PM

BrenBart SSCrazy Points: 2622 More actions · Answer 11

I tried something similar to coondapoor's approach but it doesn't handle a string of all zeros. i.e. '000' yields '000'

Hence the case statement...

"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)

Calvin Lawson SSChampion Points: 11030 More actions · Answer 12

It depends on the functionality you want. how to handle non-numberic values. Coondapoor's function will trim all non-numberic values off the end of the number, but will leave them if their in the middle.

declare @var1 varchar(25),

@var2 varchar(25),

@var3 varchar(25)

SELECT @var1 = '0000PPP105231',

@var2 = '00001105231',

@var3 = '0000110PPP231'

--Will trim zeros off non-numeric values

select SUBSTRinG(@var1,PATINDEX('%[1-9]%',@var1),LEN(@var1)-PATINDEX('%[1-9]%',@var1)+1)

select SUBSTRinG(@var2,PATINDEX('%[1-9]%',@var2),LEN(@var1)-PATINDEX('%[1-9]%',@var2)+1)

select SUBSTRinG(@var3,PATINDEX('%[1-9]%',@var3),LEN(@var1)-PATINDEX('%[1-9]%',@var3)+1)

--Will NULL out non-numberic values

select case isnumeric(@var1) When 1 then cast(@var1 as int) Else NULL END

select case isnumeric(@var2) When 1 then cast(@var2 as int) Else NULL END

select case isnumeric(@var3) When 1 then cast(@var3 as int) Else NULL END

Signature is NULL

dba_user Default port Points: 1458 More actions · Answer 13

Thanks for all your help!

Upon research, I realize that this table was designed poorly. Multiple apps supply the need for multiple data types on this field on the table. It is quite a mess. However, I fixed the problem by creating a view of the table and changing the field to integer in the view to get rid of the zero's. Then changed the app to point at the view instead of the table. It's not what I like due to inconsistency and the need for good documentation. However, it works for now!

Thanks again for all your help!