Generate a Random String

[JimKnicely]

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!

[JimKnicely]

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

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