RANDSTR

Syntax

RANDSTR(N, gen)

Purpose

This 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 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. The max_allowed_packet variable specifies the maximum size of a packet. Its default value is 16777216.

• gen specifies the distribution function. Valid values:

  • 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 distribution, Zipf distribution, or Gaussian distribution.

• The random string is generated by using the LCG algorithm to generate N random values, and then selecting N characters based on these values. Therefore, when N is large, the CPU usage is high. For example, generating a random string of length 300 M 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 allows you to control the frequency of random string generation by using the gen parameter. The following example generates 1000 strings of length 1. The distribution of these strings follows 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 allows you to generate strings of different lengths by controlling the value of the N parameter. The following example shows how to do this.

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 called. The following example shows this.

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