Generate a Random String
Posted: 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:
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:
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:
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:
And the next query will craft a random string with 8 letters:
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!
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
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)
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)
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)
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)
viewtopic.php?f=52&t=134&p=217
viewtopic.php?f=48&t=160
Have fun!