Post reply

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

  • 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

  • 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%';

  • 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.

  • 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

  • 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

  • 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?

  • 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.

  • 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%'

  • 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;

  • 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

  • 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??

  • 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?

  • 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

  • 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, ''))

  • 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?

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy