none
Trim leading zeros

    Question

  • My records are like this.

    Col1

    -----

    00001

    03456

    00577

    05011

    00099

    01090

     

    I want to remove the zeros on the left and the answer should be like this.

     

    Col1

    -----

    1

    3456

    577

    5011

    99

    1090

     

    How to trim the leading zeros.

    Thanks.

     

    Thursday, February 28, 2008 7:44 PM

Answers

  • see the following website:

     

    http://www.sql-server-helper.com/functions/trim-leading-zeros.aspx

     

    I believe the below script might be beneficial to your situation. I would recommend trying on backup data first.

     

    UPDATE [dbo].[Table]
    SET [Column] = REPLACE(LTRIM(REPLACE([Column], '0', ' ')), ' ', '0')

     

     

    Thursday, February 28, 2008 10:03 PM
  • Code Snippet
    SELECT (CAST Col1 AS int) AS Col1

     

     

     

    and if you need it to be back into character format....

     

    Code Snippet
    SELECT (CAST(CAST Col1 AS int) AS varchar(length)) AS Col1

     

     

     

    Thursday, February 28, 2008 7:48 PM
    Moderator

All replies

  • Code Snippet
    SELECT (CAST Col1 AS int) AS Col1

     

     

     

    and if you need it to be back into character format....

     

    Code Snippet
    SELECT (CAST(CAST Col1 AS int) AS varchar(length)) AS Col1

     

     

     

    Thursday, February 28, 2008 7:48 PM
    Moderator
  • see the following website:

     

    http://www.sql-server-helper.com/functions/trim-leading-zeros.aspx

     

    I believe the below script might be beneficial to your situation. I would recommend trying on backup data first.

     

    UPDATE [dbo].[Table]
    SET [Column] = REPLACE(LTRIM(REPLACE([Column], '0', ' ')), ' ', '0')

     

     

    Thursday, February 28, 2008 10:03 PM
  • not so safe, this suppose that all field are numerical and shorter than maxint is. If you want just to trim padding zero you should follow this http://www.sql-server-helper.com/functions/trim-leading-zeros.aspx that will work even if some fields are compiled like this "00023B" or "0022-1". Also you can have overflow error if the string is too long like this "select cast('0012345678901' as int)" 

    Thursday, November 22, 2012 1:42 PM
  • Use this

      SELECT [Col1]
          ,SUBSTRING([Col1], PATINDEX('%[^0 ]%', [Col1] + ' '), LEN([Col1])) as Col1_WO_ZERO
      FROM Table1

    Monday, March 10, 2014 11:13 AM
  • not so safe, this suppose that all field are numerical and shorter than maxint is. If you want just to trim padding zero you should follow this http://www.sql-server-helper.com/functions/trim-leading-zeros.aspx that will work even if some fields are compiled like this "00023B" or "0022-1". Also you can have overflow error if the string is too long like this "select cast('0012345678901' as int)" 


    If its sql 2012, you can still go ahead and use TRY_CONVERT which will not cause any error but will return NULL for invalid or out of range values. you could just apply a logic to replace those NULLs with actual values (using COALESCE,ISNULL etc) and get the result.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, March 10, 2014 11:27 AM
  • another method..

    declare @t table(num varchar(10))
    
    insert @t select '0009'
    insert @t select '9009'
    insert @t select '0088'
    insert @t select '0778'
    insert @t select '1233'
    insert @t select '0000'
    insert @t select NULL
    
    select * from @t
    
    UPDATE @t
    SET num= CASE WHEN PATINDEX('%[1-9]%',num)=0 THEN '0' ELSE STUFF(num,1,PATINDEX('%[1-9]%',num)-1,'') END 
    
    select * from @t


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Monday, March 10, 2014 12:27 PM
  • With SQL Server 2012, you can use Try_Parse or Try_Convert functions.

    select try_parse('000123'as int), try_convert(int, '000' )

    Monday, March 10, 2014 2:33 PM
    Moderator
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Are you always this rude to people? I would guess you meant this:

    CREATE TABLE Rude_Posters
    (i CHAR(5) NOT NULL PRIMARY KEY 
       CHECK (i LIKE '[0-9][0-9][0-9][0-9][0-9]'));

    UPDATE Rude_Posters
      SET i
         = CASE WHEN i = '00000' THEN ''
                WHEN i LIKE '0000_'  THEN SUBSTRING(i,5,5)
                WHEN i LIKE '000__'  THEN SUBSTRING(i,4,5)
                WHEN i LIKE '00___'  THEN SUBSTRING(i,3,5)
                WHEN i LIKE '0____'  THEN SUBSTRING(i,2,5)
                ELSE i END;




    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, March 10, 2014 9:44 PM