Generate a Random String

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Generate a Random String

Post by JimKnicely » Fri Mar 15, 2013 8:43 pm

Hi!

There is a really useful database package in Oracle named DBMS_RANDOM which includes a function named STRING that can be used to return a random string of characters.

Here’s an example from Oracle:

Code: Select all

SQL> SELECT dbms_random.string('U', 10) "A Random String"
  2    FROM dual;

A Random String
---------------
TXEBZXOTQE
Notice that the DBMS_RANDOM.STRING function accepts two parameters. In the example, the first parameter “U” limits the output to uppercase letters only, while the second parameter 10 indicates the number of characters to string together.

Vertica doesn’t have a built-in function for it, but it is possible to generate a random string of characters!

First off, it’s relatively easy to produce a single random uppercase letter using the CHR and RANDOMINT functions like this:

Code: Select all

dbadmin=> SELECT CHR(RANDOMINT(25) + 65) "Random Letter";
Random Letter
---------------
M
(1 row)

Code: Select all

dbadmin=> SELECT CHR(RANDOMINT(25) + 65) "Random Letter";
Random Letter
---------------
T
(1 row)

Code: Select all

dbadmin=> SELECT CHR(RANDOMINT(25) + 65) "Random Letter";
Random Letter
---------------
X
(1 row)
However, to paste together a bunch of random letters takes a little work.

One method is to simply concatenate a bunch of randomly generated letters together:

Code: Select all

dbadmin=> SELECT CHR(RANDOMINT(25) + 65) || CHR(RANDOMINT(25) + 65) ||
dbadmin->        CHR(RANDOMINT(25) + 65) || CHR(RANDOMINT(25) + 65) || CHR(RANDOMINT(25) + 65) "A Random String";
A Random String
-----------------
PKJDA
(1 row)
Another method is a little more creative. In the query below we can specify the number of random letters to string together.

For instance, the following query will produce a random string with 5 letters:

Code: Select all

dbadmin=> SELECT MAX(DECODE(rn, 1, letter)) ||
dbadmin->        NVL(MAX(DECODE(rn, 2, letter)), '') ||
dbadmin->        NVL(MAX(DECODE(rn, 3, letter)), '') ||
dbadmin->        NVL(MAX(DECODE(rn, 4, letter)), '') ||
dbadmin->        NVL(MAX(DECODE(rn, 5, letter)), '') ||
dbadmin->        NVL(MAX(DECODE(rn, 6, letter)), '') ||
dbadmin->        NVL(MAX(DECODE(rn, 7, letter)), '') ||
dbadmin->        NVL(MAX(DECODE(rn, 8, letter)), '') ||
dbadmin->        NVL(MAX(DECODE(rn, 9, letter)), '') ||
dbadmin->        NVL(MAX(DECODE(rn, 10,letter)), '') "A Random String"
dbadmin->  FROM (SELECT row_number() over() rn, CHR(RANDOMINT(25) + 65) letter FROM tables) foo
dbadmin-> WHERE rn <= 5;
A Random String
-----------------
TPUCO
(1 row)
And the next query will craft a random string with 8 letters:

Code: Select all

dbadmin=> SELECT MAX(DECODE(rn, 1, letter)) ||
dbadmin->        NVL(MAX(DECODE(rn, 2, letter)), '') ||
dbadmin->        NVL(MAX(DECODE(rn, 3, letter)), '') ||
dbadmin->        NVL(MAX(DECODE(rn, 4, letter)), '') ||
dbadmin->        NVL(MAX(DECODE(rn, 5, letter)), '') ||
dbadmin->        NVL(MAX(DECODE(rn, 6, letter)), '') ||
dbadmin->        NVL(MAX(DECODE(rn, 7, letter)), '') ||
dbadmin->        NVL(MAX(DECODE(rn, 8, letter)), '') ||
dbadmin->        NVL(MAX(DECODE(rn, 9, letter)), '') ||
dbadmin->        NVL(MAX(DECODE(rn, 10,letter)), '') "A Random String"
dbadmin->  FROM (SELECT row_number() over() rn, CHR(RANDOMINT(25) + 65) letter FROM tables) foo
dbadmin-> WHERE rn <= 8;
A Random String
-----------------
PQKOXQTR
(1 row)
Check out the following topics to learn how the group_concat function can help make the above queries even better!

viewtopic.php?f=52&t=134&p=217
viewtopic.php?f=48&t=160

Have fun!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Generate a Random String

Post by JimKnicely » Sat Mar 16, 2013 5:51 pm

Here's a link to a great solution from our community member skwa!

https://github.com/sKwa/vertica/blob/ma ... String.cpp
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”