RANDSTR

Syntax

RANDSTR(N, gen)

Purpose

RANDSTR() generates a random string of a specified length. The string can contain uppercase letters (A to Z), lowercase letters (a to z), and digits (0 to 9).

• N specifies the length of the generated random string.

  • When N < 0, NULL is returned.
  • When N = 0, an empty string is returned.
  • When N > 0 and N <= max_allowed_packet, a random string of the length specified by N is returned.
  • When N > max_allowed_packet, NULL is returned. max_allowed_packet is a system variable with a default value of 16777216.

• gen is a distribution function. Valid values are as follows:

  • A constant. In this case, RANDSTR() always generates the same random string when it is called.
  • A random function that returns an integer. For example, RANDOM().
  • A distribution function, such as a uniform distribution, Zipf distribution, or Gaussian distribution function.

• Random string generation process: A linear congruential generator (LCG) generates N random values. Then N characters are selected based on the random values to form a random string. Therefore, when N is large, significant CPU resources are consumed. The following example generates a random string with the length of 300,000,000 and takes about 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 uses the RANDSTR() function to generate a random string with the length of 10.

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 at different frequencies by controlling the value of the gen parameter. The following example generates 1000 strings at the length of 1 from the Zipf distribution.

obclient> SELECT RANDSTR(1, ZIPF(1, 10, RANDOM())) s, COUNT(*) FROM TABLE(GENERATOR(1000)) GROUP BY s ORDER BY 1 ASC;
+------+----------+
| s    | COUNT(*) |
+------+----------+
| 0    |       27 |
| 1    |       41 |
| 4    |       32 |
| 5    |       54 |
| 8    |       64 |
| 9    |       69 |
| C    |       88 |
| D    |      125 |
| G    |      168 |
| H    |      332 |
+------+----------+
10 rows in set

The RANDSTR() function can generate strings of different lengths by controlling 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 always generates the same random string, 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