RANDSTR

Syntax

RANDSTR(N, gen)

Purpose

The RANDSTR() function generates a random string of the specified length. The characters in the string include uppercase letters (A-Z), lowercase letters (a-z), and digits (0-9).

• N specifies the length of the random string to generate:

  • If N < 0, NULL is returned.
  • If N = 0, an empty string is returned.
  • If N > 0 and N <= max_allowed_packet, a random string of length N is returned.
  • If N > max_allowed_packet, NULL is returned. The default value of max_allowed_packet is 16777216.

• gen specifies the distribution function, which can be:

  • A constant. In this case, the same random string is generated each time the RANDSTR() function is called.
  • A random function that returns an integer value, such as RANDOM().
  • A distribution function, such as uniform, Zipf, or Gaussian distribution.

• The random string is generated using the LCG algorithm to generate N random values, which are then used to select N characters to form the random string. Therefore, when N is large, a significant amount of CPU resources is consumed. For example, the following statement generates a random string of length 300 M, which takes 1.7 seconds.

  obclient> SELECT LENGTH(RANDSTR(300000000, 1)) FROM DUAL;;
  +------------------------------+
  | LENGTH(RANDSTR(300000000,1)) |
  +------------------------------+
  |                    300000000 |
  +------------------------------+
  1 row in set (1.73 sec)
  

Examples

The following example generates a random string of length 10 by using the RANDSTR() function.

obclient> SELECT RANDSTR(10, RANDOM()) FROM TABLE(GENERATOR(7));
+-----------------------+
| randstr(10, random()) |
+-----------------------+
| TGfdBAZ1BP            |
| VvMbD2bChR            |
| VNSB6MpgQw            |
| 3QWYQYQVJ4            |
| CpyE58jM0o            |
| vBvsJBGGmX            |
| rg3Pkde0UJ            |
+-----------------------+
7 rows in set

The RANDSTR() function can generate strings of different lengths by varying the value of the N parameter, as shown in the following example.

obclient> SELECT RANDSTR(5, 40) FROM DUAL;
+----------------+
| RANDSTR(5, 40) |
+----------------+
| UU3g0          |
+----------------+
1 row in set

obclient> SELECT RANDSTR(1, 40) FROM DUAL;
+----------------+
| RANDSTR(1, 40) |
+----------------+
| U              |
+----------------+
1 row in set

obclient> SELECT RANDSTR(0, 40) FROM DUAL;
+----------------+
| RANDSTR(0, 40) |
+----------------+
|                |
+----------------+
1 row in set

obclient> SELECT RANDSTR(-1, 40) FROM DUAL;
+-----------------+
| RANDSTR(-1, 40) |
+-----------------+
| NULL            |
+-----------------+
1 row in set

When gen is a constant, the RANDSTR() function generates the same random string each time it is executed, as shown in the following example.

obclient> SELECT RANDSTR(10, 1442) FROM TABLE(GENERATOR(7));
+-------------------+
| RANDSTR(10, 1442) |
+-------------------+
| 4dTm5bOtGc        |
| 4dTm5bOtGc        |
| 4dTm5bOtGc        |
| 4dTm5bOtGc        |
| 4dTm5bOtGc        |
| 4dTm5bOtGc        |
| 4dTm5bOtGc        |
+-------------------+
7 rows in set

obclient> SELECT RANDSTR(10, 1442) FROM TABLE(GENERATOR(7));
+-------------------+
| RANDSTR(10, 1442) |
+-------------------+
| 4dTm5bOtGc        |
| 4dTm5bOtGc        |
| 4dTm5bOtGc        |
| 4dTm5bOtGc        |
| 4dTm5bOtGc        |
| 4dTm5bOtGc        |
| 4dTm5bOtGc        |
+-------------------+
7 rows in set