Remove Leading Zeros

  • 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!!

  • 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

  • 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

    */

  • 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)

  • 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)

  • 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

  • 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]

  • 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.

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

  • declare @var1 varchar(10)

    SELECT @var1 = '1105231'

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

  • Very nice, BrenBart and coondapoor!

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

  • 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)

  • 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

  • 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!

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

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