insert random value from predefined list of possible values

Question

Post reply

insert random value from predefined list of possible values

marchello

Old Hand

Points: 355
More actions
April 6, 2020 at 5:06 pm

#3740835

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.

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

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

pietlinden SSC Guru Points: 63572 More actions · Answer 1

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

Steve Collins SSCrazy Points: 2197 More actions · Answer 2

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

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004588 More actions · Answer 3

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)

marchello Old Hand Points: 355 More actions · Answer 4

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: 1004588 More actions · Answer 5

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)

Steve Collins SSCrazy Points: 2197 More actions · Answer 6

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?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004588 More actions · Answer 7

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)

marchello Old Hand Points: 355 More actions · Answer 8

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.

insert random value from predefined list of possible values

Cookies on SQLServerCentral