Post reply

Update with Recursive CTE?

  • March 18, 2015 at 3:26 pm

    #296168

    I have a list of account numbers stored in a table and am trying to update all account numbers to be 10 digits long. Some accounts will be 8 digits, some more, some less. If the account number is less than 10 digits, then we need to prepend 0's on the front of the account to make it 10 digits.

    My thought was to find all rows where account number is less than 10 digits and add a 0, then check again for all rows less than 10 digits and add another 0, etc. until there are no more rows left where account number is less than 10 digits.

    It seems I should be able to do this with a recursive CTE, but I am very new to CTEs and not certain this will solve the issue.

    Can anyone confirm if a recursive CTE would be the best way to do this?

    Any suggestions would be much appreciated! Thanks in advance!

  • March 18, 2015 at 4:15 pm

    #1784932

    That sounds like a very painful way to pad a string.

    RIGHT('0000000000' + YOUR_ACCOUNT_NUMBER, 10)

  • March 18, 2015 at 4:40 pm

    #1784938

    Or using replicate to know how many zeroes you're using without counting them. It's a matter of personal preference.

    SELECT RIGHT(REPLICATE('0', 10) + RTRIM( YOUR_ACCOUNT_NUMBER), 10)

    FROM (SELECT CAST( ABS(CHECKSUM(NEWID())) % 10000000 + 1 AS CHAR(10)) --Generating sample data

    FROM sys.objects)x(YOUR_ACCOUNT_NUMBER)

    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 18, 2015 at 5:18 pm

    #1784947

    isaac.asher (3/18/2015)

    I have a list of account numbers stored in a table and am trying to update all account numbers to be 10 digits long. Some accounts will be 8 digits, some more, some less. If the account number is less than 10 digits, then we need to prepend 0's on the front of the account to make it 10 digits.

    My thought was to find all rows where account number is less than 10 digits and add a 0, then check again for all rows less than 10 digits and add another 0, etc. until there are no more rows left where account number is less than 10 digits.

    It seems I should be able to do this with a recursive CTE, but I am very new to CTEs and not certain this will solve the issue.

    Can anyone confirm if a recursive CTE would be the best way to do this?

    Any suggestions would be much appreciated! Thanks in advance!

    You've got a couple of answers now but I'm curious. What is the data type of the account number column that the original data lives in?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 19, 2015 at 8:53 am

    #1785087

    My idea was most definitely painful...... and I figured someone out there had a simple solution. Mr or Mrs 500 had exactly the solution I needed! Thank you so very much!

    @jeff - I had to use varchar data type as the account number includes dashes which are vital to the structure of the account number. Would be curious to hear where you're going with this.....

    Thanks everyone! 😀

  • March 19, 2015 at 6:06 pm

    #1785254

    isaac.asher (3/19/2015)

    My idea was most definitely painful...... and I figured someone out there had a simple solution. Mr or Mrs 500 had exactly the solution I needed! Thank you so very much!

    @jeff - I had to use varchar data type as the account number includes dashes which are vital to the structure of the account number. Would be curious to hear where you're going with this.....

    Thanks everyone! 😀

    I was going to suggest a slightly different method of left padding but only if a numeric datatype was the source column and you just wanted to display leading zeroes. I think it was Dwain Camps that showed me the trick. Unfortunately, I'm pretty sure that most bank account numbers exceed the limits of the technique.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 19, 2015 at 6:12 pm

    #1785255

    This is a quiz. Which is faster?

    This:

    RIGHT('0000000000' + YOUR_ACCOUNT_NUMBER, 10)

    Or this:

    RIGHT(10000000000 + YOUR_ACCOUNT_NUMBER, 10)

    No need to wait for the answer: The One Million Row T-SQL Test Harness[/url]

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • March 19, 2015 at 6:13 pm

    #1785256

    Jeff Moden (3/19/2015)

    isaac.asher (3/19/2015)

    My idea was most definitely painful...... and I figured someone out there had a simple solution. Mr or Mrs 500 had exactly the solution I needed! Thank you so very much!

    @jeff - I had to use varchar data type as the account number includes dashes which are vital to the structure of the account number. Would be curious to hear where you're going with this.....

    Thanks everyone! 😀

    I was going to suggest a slightly different method of left padding but only if a numeric datatype was the source column and you just wanted to display leading zeroes. I think it was Dwain Camps that showed me the trick. Unfortunately, I'm pretty sure that most bank account numbers exceed the limits of the technique.

    Oh shoot! I stole your thunder!

    Sorry about that! At least you now know where to find a test harness so you don't have to construct one every time you need to prove your point.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • March 19, 2015 at 9:08 pm

    #1785263

    NP. At least this ol' brain remembered who showed me. Thanks for the confirmation. 😛

    The numeric method won't work on VARCHARs that have non-digit-only characters in them like what the op has.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 19, 2015 at 9:12 pm

    #1785264

    Jeff Moden (3/19/2015)

    NP. At least this ol' brain remembered who showed me. Thanks for the confirmation. 😛

    The numeric method won't work on VARCHARs that have non-digit-only characters in them like what the op has.

    Obviously I missed the part about the accounts containing dashes too!

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 10 posts - 1 through 9 (of 9 total)

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