Download

Version 0.3

Previous Versions

Information

A library of string functions complementing the set of native ones provided by MySQL.

about

MySQL UDFs offer a powerful way to extend the functionality of your MySQL database.

The UDFs on this site are all free: free to use, free to distribute, free to modify and free of charge. Even for commercial projects.

Blogroll

Roland Bouman

Jasny - Blazing fast websites

O2 broadband

See also

lib_mysqludf_str

MySQL already supports a number of string functions natively. However, these string functions are not exhaustive and other string functions can ease the development of MySQL-based applications. Users coming from a PHP or Perl background, for instance, may expect to find their entire set of string functions in MySQL. lib_mysqludf_str aims to offer a library of string functions which complement the native ones.

The following functions are currently supported in the lib_mysqludf_str library:

  1. str_numtowords - converts a number to a string.
  2. str_rot13 - performs the rot13 transform on a string.
  3. str_shuffle - randomly shuffles the characters of a string.
  4. str_translate - replaces characters contained in srcchar with the corresponding ones in dstchar.
  5. str_ucfirst - makes uppercase the first character of the string.
  6. str_ucwords - transforms to uppercase the first character of each word in a string.
  7. str_xor - performs a byte-wise exclusive OR (XOR) of two strings.
  8. str_srand - generates a string of cryptographically secure pseudo-random bytes.

Use lib_mysqludf_str_info() to obtain information about the currently-installed version of lib_mysqludf_str.

str_numtowords

str_numtowords converts numbers written in arabic digits to an english word. All integers in the range [-263, 263 - 1] are supported.

Syntax

str_numtowords(num)

Parameters and Return Values

num
The integer number to be converted to string. If num is not an integer type or it is NULL, an error will be returned.
returns
The string spelling the given number in English.

Examples

Converting 123456 to string: 

SELECT str_numtowords(123456) as price;

yields this result:

+----------------------------------------------------------+
| price                                                    |
+----------------------------------------------------------+
| one hundred twenty-three thousand four hundred fifty-six |
+----------------------------------------------------------+

str_rot13

str_rot13 performs the rot13 transform on a string, shifting each character by 13 places in the alphabet, and wrapping back to the beginning if necessary. Non-alphabetic characters are not modified.

Syntax

str_rot13(subject)

Parameters and Return Values

subject
The string to be transformed. If subject is not a string type or it is NULL, an error will be returned.
returns
The original string with each character shifted by 13 places in the alphabet.

Examples

Crypting a string using str_rot13: 

SELECT str_rot13('secret message') as crypted;

yields this result:

+----------------+
| crypted        |
+----------------+
| frperg zrffntr |
+----------------+

Decrypting a rot13-encoded string (applying again the same function): 

SELECT str_rot13('frperg zrffntr') as decrypted;

yields this result:

+----------------+
| decrypted      |
+----------------+
| secret message |
+----------------+

str_shuffle

The str_shuffle function takes a string and randomly shuffles its characters, returning one of its possible permutations.

Syntax

str_shuffle(subject)

Parameters and Return Values

subject
A string value to be shuffled. If str is not a string type or it is NULL, an error will be returned.
returns
A string value representing one of the possible permutations of the characters composing subject.

Examples

Shuffling a string: 

SELECT str_shuffle('shake me!') as nonsense;

yields a result like this:

+-----------+
| nonsense  |
+-----------+
| esm a!khe |
+-----------+

str_translate

The str_translate function scans each character in subject string and replaces every occurrence of a character that is contained in srcchar with the corresponding char in dstchar.

Syntax

str_translate(subject,srcchar, dstchar)

Parameters and Return Values

subject
A string value whose characters have to be transformed. If subject is not a string type or it is NULL, an error will be returned.
srcchar
A string value containing the characters to be searched and replaced, if present. If srcchar is not a string type or it is NULL, an error will be returned. srcchar must contain the same number of characters of dstchar.
dstchar
A string value containing the characters which will replace the corresponding ones in srcchar. If dstchar is not a string type or it is NULL, an error will be returned. dstchar must contain the same number of characters of srcchar.
returns
A string value that is a copy of subject but in which each character present in srcchar is replaced with the corresponding one in dstchar.

Examples

Replacing 'a' with 'x' and 'b' with 'y': 

SELECT str_translate('a big string', 'ab', 'xy');

yields this result:

+--------------+
| translated   |
+--------------+
| x yig string |
+--------------+

str_ucfirst

The str_ucfirst function is the MySQL equivalent for PHP's ucfirst(). It takes a string and transforms its first characters into uppercase.

Syntax

str_ucfirst(subject)

Parameters and Return Values

subject
A string value whose first character will be transformed into uppercase. If subject is not a string type or it is NULL, an error will be returned.
returns
A string value with the first character of subject capitalized, if that character is alphabetic.

Examples

Capitalizing a string: 

SELECT str_ucfirst('sample string') as capitalized;

yields this result:

+---------------+
| capitalized   |
+---------------+
| Sample string |
+---------------+

See Also

str_ucwords

The str_ucwords function is the MySQL equivalent for PHP's ucwords(). It takes a string and transforms the first character of each of its word into uppercase.

Syntax

str_ucwords(subject)

Parameters and Return Values

subject
A string value where the first character of each string will be transformed into uppercase. If subject is not a string type or it is NULL, an error will be returned.
returns
A string value with the first character of each word in subject capitalized, if that character is alphabetic.

Examples

Capitalizing the first character of each word in a string: 

SELECT str_ucwords('a string composed of many words') as capitalized;

yields this result:

+---------------------------------+
| capitalized                     |
+---------------------------------+
| A String Composed Of Many Words |
+---------------------------------+

See Also

str_xor

The str_xor function performs a byte-wise exclusive OR (XOR) of two strings.

Syntax

str_xor(string1, string2)

Parameters and Return Values

string1
The first string. If string1 is not a string or is NULL, then an error is returned.
string2
The second string. If string2 is not a string or is NULL, then an error is returned.
returns
The string value that is obtained by XORing each byte of string1 with the corresponding byte of string2.

Note that if string1 or string2 is longer than the other, then the shorter string is considered to be padded with enough trailing NUL bytes (0x00) for the two strings to have the same length.

Examples

SELECT HEX(str_xor(UNHEX('0E33'), UNHEX('E0'))) as result;

yields this result:

+--------+
| result |
+--------+
| EE33   |
+--------+
	
SELECT HEX(str_xor('Wiki', UNHEX('F3F3F3F3'))) as result;

yields this result:

+----------+
| result   |
+----------+
| A49A989A |
+----------+

Since

Version 0.2

See Also

str_srand

The str_srand function generates a string of random bytes from a cryptographically secure pseudo random number generator (CSPRNG).

Syntax

str_srand(length)

Parameters and Return Values

length

The number of pseudo-random bytes to generate, and the length of the string. If length is not a non-negative integer or is NULL, then an error is returned.

Note: To prevent Denial of Service, length is limited to the compile-time constant MAX_RANDOM_BYTES. By default, MAX_RANDOM_BYTES is 4096 (4 KiB).

returns
A string value comprised of length cryptographically secure pseudo-random bytes.

Examples

SELECT str_srand(5) as result;

yields a random string containing 5 bytes.

mysql> SELECT LENGTH(str_srand(5)) as len;
+-----+
| len |
+-----+
|   5 |
+-----+

Since

Version 0.3

See Also

lib_mysqludf_str_info

The lib_mysqludf_str_info function returns information about the currently-installed version of lib_mysqludf_str.

Syntax

lib_mysqludf_str_info()

Parameters and Return Values

returns
A string value containing the version of lib_mysqludf_str that is installed.

Examples

SELECT lib_mysqludf_str_info() as info;

yields this result:

+------------------------------+
| info                         |
+------------------------------+
| lib_mysqludf_str version 0.3 |
+------------------------------+