Post reply

Checking and removing trailing zeros in varchar field

  • January 6, 2012 at 11:19 am

    #248605

    Hello All,

    I have a question which may be simple to answer and i could be way over thinking this but the situation is as follows. I have a varchar field which is holding SKU numbers. one specific company adds 3 trailing zeros to all their SKU numbers. I would like to write a function to basically truncate the last 3 characters if they happen to be '000'. The developer before me had some code that basically just cut off the last 3 without checking them, as you can imagine since not ALL SKU numbers ended with 000 this caused somewhat of a disaster and also caused said developer to be looking for another job. Can someone please let me know how i can take the entire col check if the right most 3 chars are 000 and if they are replace them with '' This is the function I have written but something seems to be off with it.

    CREATE FUNCTION [dbo].[udf_TrimTrailingZeros] (@Input VARCHAR(50))

    RETURNS VARCHAR(50)

    AS

    BEGIN

    RETURN REPLACE(RTRIM(REPLACE(@Input, '000', ' '))

    END

    Is there something wrong with this function? If so any help on what that may be would be greatly appreciated.

    Thanks,

    JT

  • January 6, 2012 at 11:25 am

    #1429776

    does a SKU have a standard length, like 6 characters?

    do you have a lookup table of all skus so you could look up whether it is valid before or after you try and trim trailing zeros? you could join the trimmed criteria to the master list and validate the SKU that way.

    can you modify your process to handle the trailing zeros separately for that one agency?

    Can you get that one vendor to fix their export?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 6, 2012 at 11:26 am

    #1429777

    Its a hard task. Can you identify the SKU based on the company that sends them? If Yes, then it becomes relatively simple but not fully covered. What if the SKU number is greater than 1000 and ends with 3 000's ? Say 1,000 or 15,000 or 20,000 ?? how do we identify that? can you give us some samples of your SKUs?

  • January 6, 2012 at 11:59 am

    #1429798

    jtrumbul (1/6/2012)

    Hello All,

    I have a question which may be simple to answer and i could be way over thinking this but the situation is as follows. I have a varchar field which is holding SKU numbers. one specific company adds 3 trailing zeros to all their SKU numbers. I would like to write a function to basically truncate the last 3 characters if they happen to be '000'. The developer before me had some code that basically just cut off the last 3 without checking them, as you can imagine since not ALL SKU numbers ended with 000 this caused somewhat of a disaster and also caused said developer to be looking for another job. Can someone please let me know how i can take the entire col check if the right most 3 chars are 000 and if they are replace them with '' This is the function I have written but something seems to be off with it.

    CREATE FUNCTION [dbo].[udf_TrimTrailingZeros] (@Input VARCHAR(50))

    RETURNS VARCHAR(50)

    AS

    BEGIN

    RETURN REPLACE(RTRIM(REPLACE(@Input, '000', ' '))

    END

    Is there something wrong with this function? If so any help on what that may be would be greatly appreciated.

    Thanks,

    JT

    Needs one more parentheses at the end. But really, the fired developer was closer, because either you have trailing superfulous '000's to all orders from this company or you have ambiguity in your requirements, ie., any given trailing '000' could either be superfulous or 1 out of each 1000 sequenced sku's that actually have a trailing '000'.

  • January 6, 2012 at 12:36 pm

    #1429821

    Lowell (1/6/2012)

    does a SKU have a standard length, like 6 characters?

    do you have a look up table of all skus so you could look up whether it is valid before or after you try and trim trailing zeros? you could join the trimmed criteria to the master list and validate the SKU that way.

    can you modify your process to handle the trailing zeros separately for that one agency?

    Can you get that one vendor to fix their export?

    Unfortunately they are not fixed length. That is pretty much the main issue with blindly trimming the last 3 chars. The rule I was told to go by is basically look at all the SKUS in the table and any time the last 3 chars are 000 truncate it. There is no master list to compare it to or validate against, they are all coming in off of an xls file. There is a process that goes with it in C# the ETL process the outline looks something like this:

    Load all the xls data into a temp table

    perform transforms on the temp table (one of the transforms being to get rid of the '000' pattern on ALL values in that field that end with '000'

    then some other transforms irrelevant to this topic.

    and finally a load into 2 separate tables based on a column designation.

  • January 6, 2012 at 12:40 pm

    #1429823

    patrickmcginnis59 (1/6/2012)

    jtrumbul (1/6/2012)

    Hello All,

    I have a question which may be simple to answer and i could be way over thinking this but the situation is as follows. I have a varchar field which is holding SKU numbers. one specific company adds 3 trailing zeros to all their SKU numbers. I would like to write a function to basically truncate the last 3 characters if they happen to be '000'. The developer before me had some code that basically just cut off the last 3 without checking them, as you can imagine since not ALL SKU numbers ended with 000 this caused somewhat of a disaster and also caused said developer to be looking for another job. Can someone please let me know how i can take the entire col check if the right most 3 chars are 000 and if they are replace them with '' This is the function I have written but something seems to be off with it.

    CREATE FUNCTION [dbo].[udf_TrimTrailingZeros] (@Input VARCHAR(50))

    RETURNS VARCHAR(50)

    AS

    BEGIN

    RETURN REPLACE(RTRIM(REPLACE(@Input, '000', ' '))

    END

    Is there something wrong with this function? If so any help on what that may be would be greatly appreciated.

    Thanks,

    JT

    Needs one more parentheses at the end. But really, the fired developer was closer, because either you have trailing superfulous '000's to all orders from this company or you have ambiguity in your requirements, ie., any given trailing '000' could either be superfulous or 1 out of each 1000 sequenced sku's that actually have a trailing '000'.

    The function listed is the one i wrote. The issue with the original developers solution was he was simply taking and removing the last 3 chars of every item in that field so while it was working for SKUS like

    12345000

    32134000

    21340000

    It was not working for skus like

    1234567

    1234012

    1234500

    Since they are not fixed length SKUS omitting the check like he had done was basically just updating the field killing off the last 3 chars no matter what they were.

  • January 6, 2012 at 12:41 pm

    #1429825

    One problem with what you posted is if you passed in a sku of 1300038 it would return 13 38.

    If you 100% certain that there are 3 and only 3 zeros at the end AND that the only skus in your dataset with 000 at the end need to be truncated. This is pretty big assumption.

    Something like this would work.

    create function udf_TrimTrailingZeros

    (

    @Input varchar(50)

    ) returns varchar(50)

    as begin

    if(RIGHT(@Input, 3) = '000')

    set @Input = Left(@Input, datalength(@Input) - 3)

    return @Input

    end

    Like I said the assumption is somewhat scary because you have to be 100% certain you don't have other skus with trailing 000.

    Once your data is cleaned up I would recommend making the data modification during import going forward.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • January 6, 2012 at 12:48 pm

    #1429829

    I would tend to do what Sean wrote. Check the right(,3) and if it's '000' remove it.

    I'd be careful, and document this in writing. I'm sure someone will find an exception and complain to you, so be sure that you've got an email that explains this is what you had to do.

  • January 6, 2012 at 12:59 pm

    #1429833

    Can this help out ?

    insert into @sku values ('12345000'),

    ('32134000'),

    ('21340000'),

    --It was not working for skus like

    ('1234567'),

    ('1234012'),

    ('1234500')

    Select sku

    , SUBSTRING ( sku , 1, datalength(sku) - patindex('%[^0]%',reverse(sku)) +1) as newsku

    --, patindex('%[^0]%',reverse(sku)) as pos

    from @sku

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • January 6, 2012 at 1:06 pm

    #1429839

    @sean i think this approach will work because the SKUS for this company alone will never end in 000 So in the code i will document that fact that if they ever change that it could be a problem but i believe this solution will work because anytime there are 3 ending zeros exactly 3 and they are the last 3 chars we want to truncate them. Thank you guys for the help. I will put this into play and let you know the results.

    -JT

  • January 6, 2012 at 1:26 pm

    #1429856

    One note on Sean's query. if the SKUs have trailing spaces, then u will not have the trailing spaces cut of.

    i may do this:

    create function udf_TrimTrailingZeros

    (

    @Input varchar(50)

    ) returns varchar(50)

    as begin

    set @Input = RTRIM(@Input)-- added rtrim

    if(RIGHT(@Input, 3) = '000')

    set @Input = Left(@Input, datalength(@Input) - 3)

    return @Input

    end

  • January 6, 2012 at 1:34 pm

    #1429860

    ColdCoffee (1/6/2012)

    One note on Sean's query. if the SKUs have trailing spaces, then u will not have the trailing spaces cut of.

    i may do this:

    create function udf_TrimTrailingZeros

    (

    @Input varchar(50)

    ) returns varchar(50)

    as begin

    set @Input = RTRIM(@Input)-- added rtrim

    if(RIGHT(@Input, 3) = '000')

    set @Input = Left(@Input, datalength(@Input) - 3)

    return @Input

    end

    Nice catch and thanks for letting us know you found a solution.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 12 posts - 1 through 11 (of 11 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