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
numis 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
subjectis 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
stringvalue to be shuffled. Ifstris not a string type or it is NULL, an error will be returned. - returns
-
A
stringvalue representing one of the possible permutations of the characters composingsubject.
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
stringvalue whose characters have to be transformed. Ifsubjectis not a string type or it is NULL, an error will be returned. srcchar-
A
stringvalue containing the characters to be searched and replaced, if present. Ifsrccharis not a string type or it is NULL, an error will be returned.srccharmust contain the same number of characters ofdstchar. dstchar-
A
stringvalue containing the characters which will replace the corresponding ones in srcchar. Ifdstcharis not a string type or it is NULL, an error will be returned.dstcharmust contain the same number of characters ofsrcchar. - returns
-
A
stringvalue that is a copy ofsubjectbut in which each character present insrccharis replaced with the corresponding one indstchar.
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
stringvalue whose first character will be transformed into uppercase. Ifsubjectis not a string type or it is NULL, an error will be returned. - returns
-
A
stringvalue with the first character ofsubjectcapitalized, 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
stringvalue where the first character of each string will be transformed into uppercase. Ifsubjectis not a string type or it is NULL, an error will be returned. - returns
-
A
stringvalue with the first character of each word insubjectcapitalized, 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 | +---------------------------------+