REPLACE( str , from_str , to_str ) Returns the string str with all occurrences of the string from_str replaced by the string to_str . REPLACE() performs a case-sensitive match when searching for from_str .

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com'

This function is multibyte safe. It returns NULL if any of its arguments are NULL . REVERSE( str ) Returns the string str with the order of the characters reversed, or NULL if str is NULL .

mysql> SELECT REVERSE('abc');
        -> 'cba'

This function is multibyte safe. RIGHT( str , len ) Returns the rightmost len characters from the string str , or NULL if any argument is NULL .

mysql> SELECT RIGHT('foobarbar', 4);
        -> 'rbar'

This function is multibyte safe. RPAD( str , len , padstr ) Returns the string str , right-padded with the string padstr to a length of len characters. If str is longer than len , the return value is shortened to len characters. If str , padstr , or len is NULL , the function returns NULL .

mysql> SELECT RPAD('hi',5,'?');
        -> 'hi???'
mysql> SELECT RPAD('hi',1,'?');
        -> 'h'

This function is multibyte safe. RTRIM( str ) Returns the string str with trailing space characters removed.

mysql> SELECT RTRIM('barbar   ');
        -> 'barbar'

This function is multibyte safe, and returns NULL if str is NULL . SOUNDEX( str ) Returns a soundex string from str , or NULL if str is NULL . Two strings that sound almost the same should have identical soundex strings. A standard soundex string is four characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All nonalphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels. This function, as currently implemented, is intended to work well with strings that are in the English language only. Strings in other languages may not produce reliable results. This function is not guaranteed to provide consistent results with strings that use multibyte character sets, including utf-8 . See Bug #22638 for more information.

mysql> SELECT SOUNDEX('Hello');
        -> 'H400'
mysql> SELECT SOUNDEX('Quadratically');
        -> 'Q36324'

mysql> SELECT SPACE(6);
        -> '      '

SUBSTR( str , pos ) , SUBSTR( str FROM pos ) , SUBSTR( str , pos , len ) , SUBSTR( str FROM pos FOR len ) SUBSTR() is a synonym for SUBSTRING() . SUBSTRING( str , pos ) , SUBSTRING( str FROM pos ) , SUBSTRING( str , pos , len ) , SUBSTRING( str FROM pos FOR len ) The forms without a len argument return a substring from string str starting at position pos . The forms with a len argument return a substring len characters long from string str , starting at position pos . The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos . In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function. A value of 0 for pos returns an empty string. For all forms of SUBSTRING() , the position of the first character in the string from which the substring is to be extracted is reckoned as

mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'

This function is multibyte safe. It returns NULL if any of its arguments are NULL . If len is less than 1, the result is the empty string. SUBSTRING_INDEX( str , delim , count ) Returns the substring from string str before count occurrences of the delimiter delim . If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim .

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

This function is multibyte safe. SUBSTRING_INDEX() returns NULL if any of its arguments are NULL . TO_BASE64( str ) Converts the string argument to base-64 encoded form and returns the result as a character string with the connection character set and collation. If the argument is not a string, it is converted to a string before conversion takes place. The result is NULL if the argument is NULL . Base-64 encoded strings can be decoded using the FROM_BASE64() function.

mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
        -> 'JWJj', 'abc'

Different base-64 encoding schemes exist. These are the encoding and decoding rules used by TO_BASE64() and FROM_BASE64() : The encoding for alphabet value 62 is '+' . The encoding for alphabet value 63 is '/' . Encoded output consists of groups of 4 printable characters. Each 3 bytes of the input data are encoded using 4 characters. If the last group is incomplete, it is padded with '=' characters to a length of 4. A newline is added after each 76 characters of encoded output to divide long output into multiple lines. Decoding recognizes and ignores newline, carriage return, tab, and space. TRIM([{BOTH | LEADING | TRAILING} [ remstr ] FROM] str ) , TRIM([ remstr FROM] str ) Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH , LEADING , or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.

mysql> SELECT TRIM('  bar   ');
        -> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
        -> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
        -> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
        -> 'barx'

This function is multibyte safe. It returns NULL if any of its arguments are NULL . UCASE( str ) UCASE() is a synonym for UPPER() . UCASE() used within views is rewritten as UPPER() . UNHEX( str ) For a string argument str , UNHEX( str ) interprets each pair of characters in the argument as a hexadecimal number and converts it to the byte represented by the number. The return value is a binary string.

mysql> SELECT UNHEX('4D7953514C');
        -> 'MySQL'
mysql> SELECT X'4D7953514C';
        -> 'MySQL'
mysql> SELECT UNHEX(HEX('string'));
        -> 'string'
mysql> SELECT HEX(UNHEX('1267'));
        -> '1267'

The characters in the argument string must be legal hexadecimal digits: '0' .. '9' , 'A' .. 'F' , 'a' .. 'f' . If the argument contains any nonhexadecimal digits, or is itself NULL , the result is NULL :

mysql> SELECT UNHEX('GG');
+-------------+
| UNHEX('GG') |
+-------------+
| NULL        |
+-------------+
mysql> SELECT UNHEX(NULL);
+-------------+
| UNHEX(NULL) |
+-------------+
| NULL        |
+-------------+

A NULL result can also occur if the argument to UNHEX() is a BINARY column, because values are padded with 0x00 bytes when stored but those bytes are not stripped on retrieval. For example, '41' is stored into a CHAR(3) column as '41 ' and retrieved as '41' (with the trailing pad space stripped), so UNHEX() for the column value returns X'41' . By contrast, '41' is stored into a BINARY(3) column as '41\0' and retrieved as '41\0' (with the trailing pad 0x00 byte not stripped). '\0' is not a legal hexadecimal digit, so UNHEX() for the column value returns NULL . For a numeric argument N , the inverse of HEX( N ) is not performed by UNHEX() . CONV(HEX( N ),16,10) instead. See the description of HEX() . If UNHEX() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex . For more information about that option, see Section 4.5.1, “mysql — The MySQL Command-Line Client” . UPPER( str ) Returns the string str with all characters changed to uppercase according to the current character set mapping, or NULL if str is NULL . The default character set is utf8mb4 .

mysql> SELECT UPPER('Hej');
        -> 'HEJ'

See the description of LOWER() for information that also applies to UPPER() . This included information about how to perform lettercase conversion of binary strings ( BINARY , VARBINARY , BLOB ) for which these functions are ineffective, and information about case folding for Unicode character sets. This function is multibyte safe. UCASE() used within views is rewritten as UPPER() . WEIGHT_STRING( str [AS {CHAR|BINARY}( N )] [ flags ]) This function returns the weight string for the input string. The return value is a binary string that represents the comparison and sorting value of the string, or NULL if the argument is NULL . It has these properties: WEIGHT_STRING( str1 ) WEIGHT_STRING( str2 ) , then str1 = ( str1 and str2 are considered equal) WEIGHT_STRING( str1 ) WEIGHT_STRING( str2 ) , then str1 < ( str1 sorts before str2 ) WEIGHT_STRING() is a debugging function intended for internal use. Its behavior can change without notice between MySQL versions. It can be used for testing and debugging of collations, especially if you are adding a new collation. See Section 10.14, “Adding a Collation to a Character Set” . This list briefly summarizes the arguments. More details are given in the discussion following the list. str : The input string expression. AS clause: Optional; cast the input string to a given type and length. flags : Optional; unused. The input string, str , is a string expression. If the input is a nonbinary (character) string such as a CHAR , VARCHAR , or TEXT value, the return value contains the collation weights for the string. If the input is a binary (byte) string such as a BINARY , VARBINARY , or BLOB value, the return value is the same as the input (the weight for each byte in a binary string is the byte value). If the input is NULL , WEIGHT_STRING() returns NULL . Examples:

mysql> SET @s = _utf8mb4 'AB' COLLATE utf8mb4_0900_ai_ci;
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| AB   | 4142    | 1C471C60               |
+------+---------+------------------------+

mysql> SET @s = _utf8mb4 'ab' COLLATE utf8mb4_0900_ai_ci;
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| ab   | 6162    | 1C471C60               |
+------+---------+------------------------+

mysql> SET @s = CAST('AB' AS BINARY);
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| AB   | 4142    | 4142                   |
+------+---------+------------------------+

mysql> SET @s = CAST('ab' AS BINARY);
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| ab   | 6162    | 6162                   |
+------+---------+------------------------+

The preceding examples use HEX() to display the WEIGHT_STRING() result. Because the result is a binary value, HEX() can be especially useful when the result contains nonprinting values, to display it in printable form:

mysql> SET @s = CONVERT(X'C39F' USING utf8mb4) COLLATE utf8mb4_czech_ci;
mysql> SELECT HEX(WEIGHT_STRING(@s));
+------------------------+
| HEX(WEIGHT_STRING(@s)) |
+------------------------+
| 0FEA0FEA               |
+------------------------+

For non- NULL return values, the data type of the value is VARBINARY if its length is within the maximum length for VARBINARY , otherwise the data type is BLOB . The AS clause may be given to cast the input string to a nonbinary or binary string and to force it to a given length: AS CHAR( N ) casts the string to a nonbinary string and pads it on the right with spaces to a length of N characters. N must be at least 1. If N is less than the length of the input string, the string is truncated to N characters. No warning occurs for truncation. AS BINARY( N ) is similar but casts the string to a binary string, N is measured in bytes (not characters), and padding uses 0x00 bytes (not spaces).

mysql> SET NAMES 'latin1';
mysql> SELECT HEX(WEIGHT_STRING('ab' AS CHAR(4)));
+-------------------------------------+
| HEX(WEIGHT_STRING('ab' AS CHAR(4))) |
+-------------------------------------+
| 41422020                            |
+-------------------------------------+
mysql> SET NAMES 'utf8mb4';
mysql> SELECT HEX(WEIGHT_STRING('ab' AS CHAR(4)));
+-------------------------------------+
| HEX(WEIGHT_STRING('ab' AS CHAR(4))) |
+-------------------------------------+
| 1C471C60                            |
+-------------------------------------+

mysql> SELECT HEX(WEIGHT_STRING('ab' AS BINARY(4)));
+---------------------------------------+
| HEX(WEIGHT_STRING('ab' AS BINARY(4))) |
+---------------------------------------+
| 61620000                              |
+---------------------------------------+

The flags clause currently is unused. If WEIGHT_STRING() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex . For more information about that option, see Section 4.5.1, “mysql — The MySQL Command-Line Client” .