Random String Generator

db2udbgirl · #1 (**permalink**) 07-27-09, 12:18

Env : DB2 9.1/AIX 5.3

Any method available in DB2 to generate a random string (as rand is for a random floating point).

Thanks in advance.

tonkuma · #2 (**permalink**) 07-27-09, 12:52

There would be no such function or method in DB2 or another DBMS.

It will be better to define more specific. For example:
- Fixed length or variable length?
- Alphabet or alphanumeric or including special characters or any hex values?
- Code set(Unicode or ascii or another)?
etc.

db2udbgirl · #3 (**permalink**) 07-27-09, 13:09

It would be fixed, alphanumeric and unicode

Eg: ddbe7f50-8348-4d9f-9c0f-38dcb3a46155

Datatype of this column is VARCHAR(64)

stolze · #4 (**permalink**) 07-27-09, 13:34

You can use the RAND() function, combined with HEX() and string concatenation.

db2udbgirl · #5 (**permalink**) 07-27-09, 17:31

That worked. Thanks

stolze · #6 (**permalink**) 07-27-09, 18:27

Someone thinking for themselves - that's really great and unfortunately rather rare these days! I was really expecting a response like "how do I do that?" :-)

sathyaram_s · #7 (**permalink**) 07-28-09, 06:57

Well done db2udbgirl ...

Can you post the code here please ?

Cheers
Sathyaram

db2udbgirl · #8 (**permalink**) 07-28-09, 09:40

db2 -x "select substr(hex(rand(1)),1,8) ||'-'|| substr(hex(rand(2)),1,4)||'-'|| substr(hex(rand(3)),1,4)||'-'|| substr(hex(rand(4)),1,4)||'-'|| substr(hex(rand(5)),1,12) from sysibm.sysdummy1"

3FE071A0-3F9C-3FE1-3FAC-3FE237E46FC8

Lenny77 · #9 (**permalink**) 07-28-09, 11:35

You can use following Random String Generator, which is working on all platforms of DB2.
I just created it and tested many times:

Quote:

with input_tbl (g_string) as
( select
varchar('1234567890-=qwertyuiop[]\asdfghjkl;''zxcvbnm,./ !@#$%^&*()_+ASDFGHJKL:"ZXCVBNM<>?', 255)
from sysibm.sysdummy1
)
,
control_tbl (cntl_str, maxpos) as
(select g_string, length(g_string)
from input_tbl
)
,
rand_string_generator(k, reallength, random_string) as
(select 0, int(rand() * 100), varchar('', 100)
from sysibm.sysdummy1
union all
select k + 1, reallength, random_string || substr(cntl_str, int(rand() * maxpos + 1), 1)
from rand_string_generator, control_tbl
where k + 1 <= reallength
)
select random_string "Rand String"
from rand_string_generator
where k = reallength

Lenny K.

Lenny77 · #10 (**permalink**) 07-28-09, 14:46

Quote:

Originally Posted by db2udbgirl

db2 -x "select substr(hex(rand(1)),1,8) ||'-'|| substr(hex(rand(2)),1,4)||'-'|| substr(hex(rand(3)),1,4)||'-'|| substr(hex(rand(4)),1,4)||'-'|| substr(hex(rand(5)),1,12) from sysibm.sysdummy1"

3FE071A0-3F9C-3FE1-3FAC-3FE237E46FC8

Is this a random ? You have to use:

Quote:

select substr(hex(rand()),1,8) ||'-'|| substr(hex(rand()),1,4)||'-'|| substr(hex(rand()),1,4)||'-'
|| substr(hex(rand()),1,4)||'-'|| substr(hex(rand()),1,12) from sysibm.sysdummy1

But even this one is not a real random string generator.

Sorry, Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech