insert random value from predefined list of possible values

  • marchello

    Old Hand

    Points: 355

    Hi all,

    My need is to insert random value from predefined list of possible values. It's data obfuscation project, thus no strict rules what value should be, rather it should be random. Each row should have its own value. When we are out of possible values (more rows comparing to amount of possible values) then values should be repeated.

    My guess is that I can just loop through list of possible values. But this is rather not random. How do I randomize the order?

    Please advise.

  • pietlinden

    SSC Guru

    Points: 62779

    Use a numeric index on the possible values and then use NEW_ID() to generate a random number?

  • scdecade

    SSC Eights!

    Points: 807

    It randomly picks one of the words

    ;with data_cte(which_word) as (
    select 'some' union all select 'word' union all select 'here')
    select top(1)
    which_word
    from
    data_cte
    order by
    newid();
  • Jeff Moden

    SSC Guru

    Points: 996493

    marchello wrote:

    Hi all,

    My need is to insert random value from predefined list of possible values. It's data obfuscation project, thus no strict rules what value should be, rather it should be random. Each row should have its own value. When we are out of possible values (more rows comparing to amount of possible values) then values should be repeated.

    My guess is that I can just loop through list of possible values. But this is rather not random. How do I randomize the order?

    Please advise.

    WHAT does the "predefined list of list of possible values" consist of?  If it doesn't matter, then I have something super simple that will work a treat... (I'll be back in a minute)...

     

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

  • marchello

    Old Hand

    Points: 355

    pietlinden

    This should work for sure.

     

    scdecade

    I believe that's exactly what I was looking for.

     

    Jeff Moden

    Well, just random strings is not exactly what is needed here (if this is what you supposed to type).

  • Jeff Moden

    SSC Guru

    Points: 996493

    That's a little different.  I thought you said random "values", not "strings".  Of course, you could convert the following to strings.

    --===== Create the list of the first 1,000,000 integers
    -- and establish a random sort order for usage.
    -- The ISNULL''s are to make the columns NOT NULL
    SELECT SortOrder = IDENTITY(INT,1,1)
    ,UniqueRandomValue = ISNULL(t.N,0)
    ,IsUsed = ISNULL(0,0)
    INTO dbo.UniqueRandomValue
    FROM dbo.fnTally(1,1000000) t
    ORDER BY NEWID()
    ;

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

  • scdecade

    SSC Eights!

    Points: 807

    marchello wrote:

    It's data obfuscation project, thus no strict rules what value should be, rather it should be random. Each row should have its own value. When we are out of possible values (more rows comparing to amount of possible values) then values should be repeated.

    May I ask if this obfuscation project has to do with obscuring database meta data contained in data access server code?

  • Jeff Moden

    SSC Guru

    Points: 996493

    I'm sorry... I forgot to add that you can get the fnTally function from the similarly named link in my signature lines below.

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

  • marchello

    Old Hand

    Points: 355

    Jeff Moden

    Oh, I googled fnTally right away. The approach is great. I would definitely use it for creating random number values. And probably will. Though I need to deal with varchar values first and I have prepared list of possible values already. Thus I would stick to scdecade's hint.

     

    scdecade

    Not sure about server access codes tbh. The project is in progress and I didn't get any task like mentioned above yet. What I know is that training team of financial company needs a bunch of tables populated with synthetic values similar to original ones. So far so good.

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

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