Remove all starting 0 from a number

Question

I have in my sql table different kinds of barcodes.

My Barcodes starting with 0 example

05212365489787
0000000290098
0000000000103

I want to remove all starting 0 from my barcodes.

Example result want to be:

5212365489787
290098
103

I found this Code: But i want to update them:

select SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col)) from table1

something like that

 update table1 set Barcode =(SELECT SUBSTRING(Barcode, PATINDEX('%[^0 ]%', Barcode + ' '), LEN(Barcode)) FROM table1 )

but i get error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

make datatype as int then all 0 will be automatically ignored — Naruto, Sep 24 '16 at 16:24
Exactly just cast the data to `INT` or `BIGINT` depending on the data — Pரதீப், Sep 24 '16 at 16:25
The accepted answer in the marked duplicate specifically will not work in this case. — Gordon Linoff, Sep 24 '16 at 16:26

score 1 · Answer 1 · answered Sep 24 '16 at 16:27

Just CAST/CONVERT the data to INT or BIGINT depending on the data all the leading zero will be removed by doing this. Because leading zeros are meaning less in a numeric value so when you convert the data to INT or BIGINT leading zero's will be removed

Select cast(Barcodes as BIGINT) as result
From yourtable

To update :

Update Yourtable
   SET Barcodes = cast(Barcodes as BIGINT)
Where Left(Barcodes,1) = '0'

score 1 · Accepted Answer · edited May 23 '17 at 12:32

This is a simple modification to the accepted answer in the marked duplicate question:

select substring(barcode, patindex('%[^0]%', barcode),
                 len(barcode))

The accepted answer there has a length of 10, which is specifically incorrect for this question.

I will note that because barcodes are of finite length, you can also convert to a decimal

select cast(barcode as decimal(38, 0))

Remove all starting 0 from a number

2 Answers2