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
– Naruto, Commented Sep 24, 2016 at 16:24
Exactly just cast the data to INT or BIGINT depending on the data — Pரதீப்
– Pரதீப், Commented Sep 24, 2016 at 16:25
The accepted answer in the marked duplicate specifically will not work in this case. — Gordon Linoff
– Gordon Linoff, Commented Sep 24, 2016 at 16:26

Community · Accepted Answer · 2017-05-23 12:32:52Z

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

Pரதீப் · Accepted Answer · 2016-09-24 16:27:53Z

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'

Collectives™ on Stack Overflow

Remove all starting 0 from a number

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related