SQL Trim前面的零

Question

我有一个ID，看起来像下面:

000000000000000091
000000000000000019
000000000000001859
000000000000001220
000000000000001220
000000000000001182
000000000000010156

我希望修剪前面的零，所以只有yhe

我需要修剪值，使其显示如下:

91
19
1859
1220
1220
1182
10156

我正在尝试做以下事情:

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

Answer 1

您可以将Substring与PatIndex一起使用，而无需进行任何转换。

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

Answer 2

如果数字是2^63标记以下的整数，则可以使用CAST()。例如:

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

结果:

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

参见运行示例:db<>fiddle。

Answer 3

可以尝试将其转换为int或bigint吗?

之类的

UPDATE TABLE_NAME SET Material = CAST(Material AS BIGINT)

输入

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