RANDSTR

Syntax

RANDSTR(N, gen)

Description

This function generates a random string of the specified length, which contains uppercase letters (A-Z), lowercase letters (a-z), and digits (0-9).

• N specifies the length of the random string to be generated:

  • 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. max_allowed_packet is a system variable with a default value of 16777216.

• gen specifies the distribution function, which can be:

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

• The algorithm for generating the random string: N random values are generated using the LCG algorithm, and then N characters are selected from these values to form the random string. Therefore, a large N value will consume more CPU resources. For example, generating a random string of length 300 M takes 1.7 seconds.

  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 using the RANDSTR() function.

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

The RANDSTR() function can control the frequency of the generated random string by using the gen parameter. The following example generates 1000 strings of length 1, which follow the Zipf distribution.

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 (0.001 sec)

The RANDSTR() function can generate strings of different lengths by controlling the value of the N parameter. The following example shows how to do this.

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

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

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

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

If gen is a constant, the same random string is generated each time the RANDSTR() function is executed. The following example shows how this works.

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

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