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.

MySQL already includes many string functions natively. However, this function repository is not exhaustive and other string functions can ease the development of MySQL-based applications.

Users coming from 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 can complement the native one.

The following functions are currently supported in the lib_mysqludf_str library:

  • str_numtowords - converts a number to a string.
  • str_rot13 - performs the rot13 transform on a string.
  • str_shuffle - randomly shuffles the characters of a string.
  • str_translate - replaces characters contained in srcchar with the corresponding ones in dstchar.
  • str_ucfirst - makes uppercase the first character of the string.
  • str_ucwords - transforms to uppercase the first character of each word in a string.

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. Positive and negative numbers up to 9 digits long 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 character will not be 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