Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103

    Unanswered: Random String Generator

    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.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  3. #3
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    It would be fixed, alphanumeric and unicode

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

    Datatype of this column is VARCHAR(64)

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can use the RAND() function, combined with HEX() and string concatenation.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    That worked. Thanks

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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?" :-)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Well done db2udbgirl ...

    Can you post the code here please ?

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    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

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    You can use following Random String Generator, which is working on all platforms of DB2.
    I just created it and tested many times:

    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.

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    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:

    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
    Last edited by Lenny77; 07-28-09 at 15:52.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •