Post reply

Update HELP

  • November 23, 2009 at 2:15 pm

    #156430

    I have phone numbers store in my database....I am trying to create a test database that makes all the phone numbers random instead of the real ones. How can I write an update to update the real numbers with fake random numbers?

    Chad

  • November 23, 2009 at 2:34 pm

    #1083169

    You could use a function like this one: http://www.sqlservercentral.com/scripts/Random+Numbers/65032/

    You can then CAST numbers to character values. Move the decimal first to get the number of digits you need.

  • November 23, 2009 at 2:36 pm

    #1083170

    You were too quick for me, Steve.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • November 23, 2009 at 2:41 pm

    #1083172

    Bob,

    I need it to output like this (789) 567-8907?

  • November 23, 2009 at 3:06 pm

    #1083181

    If you generate a set of random numbers, you can use Substring to extract out a set, like the first 3, and prepend and append parens.

    select '(' + substring( randomstring, 1, 3) + ')'

    You can expand that to add in the space and hyphen as well. If you then put this in the SET clause of the update statement, you can update your table.

  • November 23, 2009 at 3:34 pm

    #1083189

    Like Steve said:

    1. Generate a 10-digit random number (ex: 1234567890)

    2. Cast it as varchar

    3. Use left,right and or/substring to build your string

    select '('+left('1234567890',3)+') '+substring('1234567890',4,3)+'-'+right('1234567890',4)

    4. Update your table with the string.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • November 23, 2009 at 9:21 pm

    #1083241

    BWAA-HAAA!!!!! C'mon guys... the cited function requires that you feed it a random number and you guys say "generate a 10 digit random number". But both of those are a part of the problem that the OP doesn't know how to solve even using RBAR never mind doing it Set Based.

    Heh... I know how you can get to the moon.... first, you build a rocket ship... 😛

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

  • November 23, 2009 at 9:37 pm

    #1083245

    cm62597 (11/23/2009)

    I have phone numbers store in my database....I am trying to create a test database that makes all the phone numbers random instead of the real ones. How can I write an update to update the real numbers with fake random numbers?

    Chad

    Chad,

    A couple of questions for you... do the phone numbers have to follow real NPA/NXX conventions so that you can find Central Office locations and the like? Or do you just need something that looks like a phone number even though it might not be a real NPA/NXX?

    Also, I have to tell you from experience, storing phone numbers in a single column instead of breaking them up by NPA/NXX/XXXX is a really bad idea especially when it comes time for an area code split. It's even a worse idea to store formatted telephone numbers. If you can't change it, you should alert the folks that make the decisions so they can change it. Having whole phone numbers in a single column is worse than storing full names in a single column because area codes change a lot more than people think.

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

  • November 24, 2009 at 8:26 am

    #1083484

    Hold on to your pork chops, Jeff. 😀

    The first thing Steve did was send him a link to a random number function. So the rocket ship was already built. All I did was show him how to turn a 10-digit number into a properly formatted string.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • November 24, 2009 at 2:54 pm

    #1083697

    update tableName

    set phonenumber = '('+cast( left( rand() * 1000,3) as varchar) + ') '

    + ''+cast( left( rand() * 1000,3) as varchar) + '-'

    + ''+cast( left( rand() * 10000,4) as varchar)

  • November 24, 2009 at 4:36 pm

    #1083740

    Bob Hovious 24601 (11/24/2009)

    Hold on to your pork chops, Jeff. 😀

    The first thing Steve did was send him a link to a random number function. So the rocket ship was already built. All I did was show him how to turn a 10-digit number into a properly formatted string.

    Heh... check that random number function... like I said before, it requires that you pass it a random number to get it to return a random number. Launch pad is built... rocket is still waiting especially if it's going to be a set based rocket. 😉

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

  • November 24, 2009 at 4:39 pm

    #1083742

    john scott miller (11/24/2009)

    update tableName

    set phonenumber = '('+cast( left( rand() * 1000,3) as varchar) + ') '

    + ''+cast( left( rand() * 1000,3) as varchar) + '-'

    + ''+cast( left( rand() * 10000,4) as varchar)

    Did you actually try that, John? It returns exactly the same phone number for all rows.

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

  • November 24, 2009 at 4:41 pm

    #1083743

    Jeff Moden (11/23/2009)

    cm62597 (11/23/2009)

    I have phone numbers store in my database....I am trying to create a test database that makes all the phone numbers random instead of the real ones. How can I write an update to update the real numbers with fake random numbers?

    Chad

    Chad,

    A couple of questions for you... do the phone numbers have to follow real NPA/NXX conventions so that you can find Central Office locations and the like? Or do you just need something that looks like a phone number even though it might not be a real NPA/NXX?

    Also, I have to tell you from experience, storing phone numbers in a single column instead of breaking them up by NPA/NXX/XXXX is a really bad idea especially when it comes time for an area code split. It's even a worse idea to store formatted telephone numbers. If you can't change it, you should alert the folks that make the decisions so they can change it. Having whole phone numbers in a single column is worse than storing full names in a single column because area codes change a lot more than people think.

    Chad... this is a really easy thing to do... I'm just waiting on you to tell me if the phone numbers can be totally fake or if they need to follow the NANPA standards for NPA/NXX.

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

  • November 24, 2009 at 5:17 pm

    #1083749

    it requires that you pass it a random number to get it to return a random number

    Maybe we could pass it ANOTHER random number to get it to randomly determine which random number to return.... :blink:

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • November 24, 2009 at 5:44 pm

    #1083754

    Heh... nah... we'll do it a different way. And apologies to you and Steve... I'll blame it on the headcold and the cough medicine... just not feeling myself and I apologize for getting froggy.

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

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

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