Generate a Random String

[Deleted User]

This blog post was authored by Jim Knicely.

You saw in a previous Vertica Quick Tip that you can create a SQL function that generates random dates. How about one that generates random strings?

Example:

=> CREATE OR REPLACE FUNCTION randomstring (x INT) RETURN VARCHAR
-> AS
-> BEGIN
->   RETURN CASE x
->            WHEN 1 THEN CHR(RANDOMINT(25) + 65)
->            WHEN 2 THEN CHR(RANDOMINT(25) + 65) || CHR(RANDOMINT(25) + 65)
->            WHEN 3 THEN CHR(RANDOMINT(25) + 65) || CHR(RANDOMINT(25) + 65) || CHR(RANDOMINT(25) + 65)
->            WHEN 4 THEN CHR(RANDOMINT(25) + 65) || CHR(RANDOMINT(25) + 65) || CHR(RANDOMINT(25) + 65) ||
->              CHR(RANDOMINT(25) + 65)
->            WHEN 5 THEN CHR(RANDOMINT(25) + 65) || CHR(RANDOMINT(25) + 65) || CHR(RANDOMINT(25) + 65) ||
->              CHR(RANDOMINT(25) + 65) || CHR(RANDOMINT(25) + 65)
->          ELSE
->            NULL
->          END;
-> END;
CREATE FUNCTION

=> SELECT randomstring(5);
randomstring
--------------
KVUSY
(1 row)

=> SELECT randomstring(5);
randomstring
--------------
FCUFS
(1 row)

=> SELECT randomstring(2);
randomstring
--------------
NI
(1 row)

Have fun!