SQL Trim Preceding zeros-CodePudding

I have an ID that looks like below:

000000000000000091
000000000000000019
000000000000001859
000000000000001220
000000000000001220
000000000000001182
000000000000010156

I am looking to trim the preceeding zero's so only yhe

I need to trim the values so it shows up like below:

I am trying to do the following:

UPDATE TABLE_NAME SET Material = RTRIM(LTRIM(REPLACE(Material,'0',' '))),' ','0'

CodePudding user response：

If the numbers are integers below the 2^63 mark you can use CAST(). For example:

with
t as (
  select '000000000000000091' as v
  union all select '000000000000000019'
  union all select '000000000000001859'
  union all select '000000000000001220'
  union all select '000000000000001220'
  union all select '000000000000001182'
  union all select '000000000000010156'
)
select ''   cast(v as bigint) from t

Result:

 (No column name) 
 ---------------- 
 91               
 19               
 1859             
 1220             
 1220             
 1182             
 10156

See running example at db<>fiddle.

CodePudding user response：

Can you try casting it to int or bigint?

Something like

UPDATE TABLE_NAME SET Material = CAST(Material AS BIGINT)

For your input

WITH DATA AS
(
    SELECT Material
    FROM
    (
        VALUES('000000000000000091'),
            ('000000000000000019'),
            ('000000000000001859'),
            ('000000000000001220'),
            ('000000000000001220'),
            ('000000000000001182'),
            ('000000000000010156')
    ) Data (Material)
)
SELECT Material, CAST(Material AS BIGINT) AS Material_Output
FROM Data

CodePudding user response：

You can use Substring with PatIndex without any conversion.

UPDATE TABLE_NAME SET Material = Substring(Material, PatIndex('%[1-9]%',Material),Len(Material))