Remove all leading zeros from a column of data in SQL?

• chef423

  SSCertifiable

  Points: 5279

  More actions

  March 15, 2017 at 11:39 am

  #341609

  I have a data table called 'ItemNum'

  The column is a SKU, which is all numbers...the current format is: 028200136107

  I need to remove all leading zeros (0) from the first digit, but I need SQL to leave the number alone IF it does not lead with a zero.

  Something like this?
  
UPDATE Inventory
  SET replace(ltrim(replace(ItemNum.Inventory, '0', '')), '', '0')
  where ItemNum = LEN(ItemNum) = 12

  
  
  Unfortunly this does not work 🙁
  
  Thanks

• Phil Parkin

  SSC Guru

  Points: 246959

  More actions

  March 15, 2017 at 11:47 am

  #1933307

  chef423 - Wednesday, March 15, 2017 11:39 AM

  I have a data table called 'ItemNum'

  The column is a SKU, which is all numbers...the current format is: 028200136107

  I need to remove all leading zeros (0) from the first digit, but I need SQL to leave the number alone IF it does not lead with a zero.

  Something like this?
  
UPDATE Inventory
SET  ItemNum REPLACE(LTRIM(REPLACE(str_col, '0', ' ')), ' ', '0')
  WHERE ItemNum LEN(ItemNum) = 12
  

  Here's one way – not fantastically fast, because of the data type conversions.

  
UPDATE Inventory
SET  ItemNum  = CAST(CAST(ItemNum AS BIGINT) AS VARCHAR(50));
WHERE ItemNum like '0%';

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• chef423

  SSCertifiable

  Points: 5279

  More actions

  March 15, 2017 at 11:53 am

  #1933308

  Phil Parkin - Wednesday, March 15, 2017 11:47 AM

  chef423 - Wednesday, March 15, 2017 11:39 AM

  I have a data table called 'ItemNum'

  The column is a SKU, which is all numbers...the current format is: 028200136107

  I need to remove all leading zeros (0) from the first digit, but I need SQL to leave the number alone IF it does not lead with a zero.

  Something like this?
  
UPDATE Inventory
SET  ItemNum REPLACE(LTRIM(REPLACE(str_col, '0', ' ')), ' ', '0')
  WHERE ItemNum LEN(ItemNum) = 12
  

  Here's one way – not fantastically fast, because of the data type conversions.

  
UPDATE Inventory
SET  ItemNum  = CAST(CAST(ItemNum AS BIGINT) AS VARCHAR(50));
WHERE ItemNum like '0%';

  Hmmm
  
UPDATE Inventory
SET ItemNum = CAST(CAST(ItemNum AS BIGINT) AS VARCHAR(50))
WHERE ItemNum like '0%'


  Msg 8114, Level 16, State 5, Line 1
  Error converting data type nvarchar to bigint.

• chef423

  SSCertifiable

  Points: 5279

  More actions

  March 15, 2017 at 11:55 am

  #1933311

  Sorry, I left out that some of the ItemNum SKU's do have letters in the ItemNum field....but I want to ignore any of those ItemNum fields...ONLY the ones that have leading zeros...

  so check all ItemNum and only drop leading 0 if it begins with a leading 0

  Tried this:
  
UPDATE Inventory
SET ItemNum = CAST(CAST(ItemNum AS BIGINT) AS VARCHAR(50))
WHERE LEN(ItemNum) = 12


• Thom A

  SSC Guru

  Points: 99129

  More actions

  March 15, 2017 at 11:56 am

  #1933312

  chef423 - Wednesday, March 15, 2017 11:53 AM

  Hmmm
  
UPDATE Inventory
SET ItemNum = CAST(CAST(ItemNum AS BIGINT) AS VARCHAR(50))
WHERE ItemNum like '0%'


  Msg 8114, Level 16, State 5, Line 1
  Error converting data type nvarchar to bigint.

  Sounds like you have some ItemNum's that aren't Numbers. Could this be true

  If not, have a look at how many rows this returns.
  SELECT ItemNum
FROM Inventory
WHERE TRY_CAST(ItemNum AS BIGINT) IS NULL;

  Edit, nevermind,OP posted again further.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• chef423

  SSCertifiable

  Points: 5279

  More actions

  March 15, 2017 at 12:02 pm

  #1933313

  Thom A - Wednesday, March 15, 2017 11:56 AM

  chef423 - Wednesday, March 15, 2017 11:53 AM

  Hmmm
  
UPDATE Inventory
SET ItemNum = CAST(CAST(ItemNum AS BIGINT) AS VARCHAR(50))
WHERE ItemNum like '0%'


  Msg 8114, Level 16, State 5, Line 1
  Error converting data type nvarchar to bigint.

  Sounds like you have some ItemNum's that aren't Numbers. Could this be true

  If not, have a look at how many rows this returns.
  SELECT ItemNum
FROM Inventory
WHERE TRY_CAST(ItemNum AS BIGINT) IS NULL;

  Edit, nevermind,OP posted again further.

  Yes, 450 rows return. Sorry, I do have some ItemNum fields that are not numbers, but I want my query to ignore those.

  I thought this may work, but it did not:
  
UPDATE Inventory
SET ItemNum = CAST(CAST(ItemNum AS BIGINT) AS VARCHAR(50))
WHERE LEN(ItemNum) = 12


  Any ideas?

• Phil Parkin

  SSC Guru

  Points: 246959

  More actions

  March 15, 2017 at 12:02 pm

  #1933314

  Borrowing Thom's code a little, try adding

  AND TRY_CAST(ItemNum AS BIGINT) IS NOT NULL

  to the WHERE clause of the code I posted earlier.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• chef423

  SSCertifiable

  Points: 5279

  More actions

  March 15, 2017 at 12:04 pm

  #1933315

  No luck with this:
  
UPDATE Inventory
SET ItemNum = CAST(CAST(ItemNum AS BIGINT) AS VARCHAR(50))
WHERE LEN(ItemNum) = 12 and ItemNum like '0%'


• Phil Parkin

  SSC Guru

  Points: 246959

  More actions

  March 15, 2017 at 12:08 pm

  #1933316

  chef423 - Wednesday, March 15, 2017 12:04 PM

  No luck with this:
  
UPDATE Inventory
SET ItemNum = CAST(CAST(ItemNum AS BIGINT) AS VARCHAR(50))
WHERE LEN(ItemNum) = 12 and ItemNum like '0%'


  Why are you persisting with that LEN test? It's not helping you weed out the non-numeric items.

  
UPDATE Inventory
SET ItemNum = CAST(CAST(ItemNum AS BIGINT) AS VARCHAR(50))
WHERE ItemNum like '0%' AND TRY_CAST(ItemNum AS BIGINT) IS NOT NULL;


  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  March 15, 2017 at 12:10 pm

  #1933318

  Here's an alternative version because it seems that your numbers are not digit-only.
  UPDATE Inventory
SET ItemNum = SUBSTRING(ItemNum, PATINDEX('%[^0]%',ItemNum), 4000)
WHERE ItemNum like '0%'

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• chef423

  SSCertifiable

  Points: 5279

  More actions

  March 15, 2017 at 12:12 pm

  #1933320

  Luis Cazares - Wednesday, March 15, 2017 12:10 PM

  Here's an alternative version because it seems that your numbers are not digit-only.
  UPDATE Inventory
SET ItemNum = SUBSTRING(ItemNum, PATINDEX('%[^0]%',ItemNum), 4000)
WHERE ItemNum like '0%'

  Msg 2627, Level 14, State 1, Line 1
  Violation of PRIMARY KEY constraint 'pkInventory'. Cannot insert duplicate key in object 'dbo.Inventory'. The duplicate key value is (1001, 12300118835).

  How could this be a violation??

• chef423

  SSCertifiable

  Points: 5279

  More actions

  March 15, 2017 at 12:29 pm

  #1933326

  chef423 - Wednesday, March 15, 2017 12:12 PM

  Luis Cazares - Wednesday, March 15, 2017 12:10 PM

  Here's an alternative version because it seems that your numbers are not digit-only.
  UPDATE Inventory
SET ItemNum = SUBSTRING(ItemNum, PATINDEX('%[^0]%',ItemNum), 4000)
WHERE ItemNum like '0%'

  Msg 2627, Level 14, State 1, Line 1
  Violation of PRIMARY KEY constraint 'pkInventory'. Cannot insert duplicate key in object 'dbo.Inventory'. The duplicate key value is (1001, 12300118835).

  How could this be a violation??

  I understand why, when its dropping the zero, there is a duplicate item number...

  How can I ignore any of the items that may be a duplicate?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  March 15, 2017 at 12:38 pm

  #1933329

  chef423 - Wednesday, March 15, 2017 12:29 PM

  chef423 - Wednesday, March 15, 2017 12:12 PM

  Luis Cazares - Wednesday, March 15, 2017 12:10 PM

  Here's an alternative version because it seems that your numbers are not digit-only.
  UPDATE Inventory
SET ItemNum = SUBSTRING(ItemNum, PATINDEX('%[^0]%',ItemNum), 4000)
WHERE ItemNum like '0%'

  Msg 2627, Level 14, State 1, Line 1
  Violation of PRIMARY KEY constraint 'pkInventory'. Cannot insert duplicate key in object 'dbo.Inventory'. The duplicate key value is (1001, 12300118835).

  How could this be a violation??

  I understand why, when its dropping the zero, there is a duplicate item number...

  How can I ignore any of the items that may be a duplicate?

  Note that this will make the update slower.
  UPDATE i
SET ItemNum = SUBSTRING( i.ItemNum, PATINDEX('%[^0]%', i.ItemNum), 4000)
FROM Inventory i
WHERE i.ItemNum like '0%'
AND SUBSTRING(i.ItemNum, PATINDEX('%[^0]%',i.ItemNum), 4000) NOT IN (SELECT x.ItemNum FROM Inventory x)

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• frederico_fonseca

  SSCoach

  Points: 16154

  More actions

  March 15, 2017 at 1:01 pm

  #1933343

  or another alternative to Luis code - wonder which one will run faster
  
UPDATE Inventory
SET ItemNum = stuff(ItemNum, 1, patindex('%[^0]%', ItemNum) - 1, '')
WHERE ItemNum like '0%'
and not exists (select 1 from Inventory i2 where ItemNum = stuff(ItemNum, 1, patindex('%[^0]%', ItemNum) - 1, ''))


• Phil Parkin

  SSC Guru

  Points: 246959

  More actions

  March 15, 2017 at 1:17 pm

  #1933345

  frederico_fonseca - Wednesday, March 15, 2017 1:01 PM

  or another alternative to Luis code - wonder which one will run faster
  
UPDATE Inventory
SET ItemNum = stuff(ItemNum, 1, patindex('%[^0]%', ItemNum) - 1, '')
WHERE ItemNum like '0%'
and not exists (select 1 from Inventory i2 where ItemNum = stuff(ItemNum, 1, patindex('%[^0]%', ItemNum) - 1, ''))


  How does this avoid touching non-numeric ItemNum values?

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin