12.8 String Functions and Operators (original) (raw)

For functions that operate on string positions, the first position is numbered 1.

For functions that take length arguments, noninteger arguments are rounded to the nearest integer.

mysql> SELECT ASCII('2');  
        -> 50  
mysql> SELECT ASCII(2);  
        -> 50  
mysql> SELECT ASCII('dx');  
        -> 100

See also the ORD() function.

mysql> SELECT BIN(12);  
        -> '1100'  
mysql> SELECT BIT_LENGTH('text');  
        -> 32  
mysql> SELECT CHAR(77,121,83,81,'76');  
        -> 'MySQL'  
mysql> SELECT CHAR(77,77.3,'77.3');  
        -> 'MMM'

CHAR() arguments larger than 255 are converted into multiple result bytes. For example,CHAR(256) is equivalent toCHAR(1,0), andCHAR(256*256) is equivalent toCHAR(1,0,0): 

mysql> SELECT HEX(CHAR(1,0)), HEX(CHAR(256));  
+----------------+----------------+  
| HEX(CHAR(1,0)) | HEX(CHAR(256)) |  
+----------------+----------------+  
| 0100           | 0100           |  
+----------------+----------------+  
mysql> SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));  
+------------------+--------------------+  
| HEX(CHAR(1,0,0)) | HEX(CHAR(256*256)) |  
+------------------+--------------------+  
| 010000           | 010000             |  
+------------------+--------------------+

By default, CHAR() returns a binary string. To produce a string in a given character set, use the optional USING clause: 

mysql> SELECT CHARSET(CHAR(X'65')), CHARSET(CHAR(X'65' USING utf8));  
+----------------------+---------------------------------+  
| CHARSET(CHAR(X'65')) | CHARSET(CHAR(X'65' USING utf8)) |  
+----------------------+---------------------------------+  
| binary               | utf8                            |  
+----------------------+---------------------------------+

If USING is given and the result string is illegal for the given character set, a warning is issued. Also, if strict SQL mode is enabled, the result fromCHAR() becomesNULL.
If CHAR() 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”.

mysql> SET @dolphin:='海豚';  
Query OK, 0 rows affected (0.01 sec)  
mysql> SELECT LENGTH(@dolphin), CHAR_LENGTH(@dolphin);  
+------------------+-----------------------+  
| LENGTH(@dolphin) | CHAR_LENGTH(@dolphin) |  
+------------------+-----------------------+  
|                6 |                     2 |  
+------------------+-----------------------+  
1 row in set (0.00 sec)  
mysql> SELECT CONCAT('My', 'S', 'QL');  
        -> 'MySQL'  
mysql> SELECT CONCAT('My', NULL, 'QL');  
        -> NULL  
mysql> SELECT CONCAT(14.3);  
        -> '14.3'

For quoted strings, concatenation can be performed by placing the strings next to each other: 

mysql> SELECT 'My' 'S' 'QL';  
        -> 'MySQL'

If CONCAT() is invoked from within the mysql client, binary string results display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, seeSection 4.5.1, “mysql — The MySQL Command-Line Client”.

mysql> SELECT CONCAT_WS(',', 'First name', 'Second name', 'Last Name');  
        -> 'First name,Second name,Last Name'  
mysql> SELECT CONCAT_WS(',', 'First name', NULL, 'Last Name');  
        -> 'First name,Last Name'

CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

mysql> SELECT ELT(1, 'Aa', 'Bb', 'Cc', 'Dd');  
        -> 'Aa'  
mysql> SELECT ELT(4, 'Aa', 'Bb', 'Cc', 'Dd');  
        -> 'Dd'  
mysql> SELECT EXPORT_SET(5,'Y','N',',',4);  
        -> 'Y,N,Y,N'  
mysql> SELECT EXPORT_SET(6,'1','0',',',10);  
        -> '0,1,1,0,0,0,0,0,0,0'  
mysql> SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');  
        -> 2  
mysql> SELECT FIELD('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');  
        -> 0  
mysql> SELECT FIND_IN_SET('b','a,b,c,d');  
        -> 2  
mysql> SELECT FORMAT(12332.123456, 4);  
        -> '12,332.1235'  
mysql> SELECT FORMAT(12332.1,4);  
        -> '12,332.1000'  
mysql> SELECT FORMAT(12332.2,0);  
        -> '12,332'  
mysql> SELECT FORMAT(12332.2,2,'de_DE');  
        -> '12.332,20'  
mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));  
        -> 'JWJj', 'abc'

If FROM_BASE64() 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, seeSection 4.5.1, “mysql — The MySQL Command-Line Client”.

mysql> SELECT X'616263', HEX('abc'), UNHEX(HEX('abc'));  
        -> 'abc', 616263, 'abc'  
mysql> SELECT HEX(255), CONV(HEX(255),16,10);  
        -> 'FF', 255  
mysql> SELECT INSERT('Quadratic', 3, 4, 'What');  
        -> 'QuWhattic'  
mysql> SELECT INSERT('Quadratic', -1, 4, 'What');  
        -> 'Quadratic'  
mysql> SELECT INSERT('Quadratic', 3, 100, 'What');  
        -> 'QuWhat'

This function is multibyte safe.

mysql> SELECT INSTR('foobarbar', 'bar');  
        -> 4  
mysql> SELECT INSTR('xbar', 'foobar');  
        -> 0

This function is multibyte safe, and is case-sensitive only if at least one argument is a binary string.

mysql> SELECT LEFT('foobarbar', 5);  
        -> 'fooba'

This function is multibyte safe.

mysql> SELECT LENGTH('text');  
        -> 4

Note
The Length() OpenGIS spatial function is named ST_Length() in MySQL.

mysql> UPDATE t  
            SET blob_col=LOAD_FILE('/tmp/picture')  
            WHERE id=1;  
mysql> SELECT LOCATE('bar', 'foobarbar');  
        -> 4  
mysql> SELECT LOCATE('xbar', 'foobar');  
        -> 0  
mysql> SELECT LOCATE('bar', 'foobarbar', 5);  
        -> 7

This function is multibyte safe, and is case-sensitive only if at least one argument is a binary string.

mysql> SELECT LOWER('QUADRATICALLY');  
        -> 'quadratically'

LOWER() (andUPPER()) are ineffective when applied to binary strings (BINARY,VARBINARY,BLOB). To perform lettercase conversion of a binary string, first convert it to a nonbinary string using a character set appropriate for the data stored in the string: 

mysql> SET @str = BINARY 'New York';  
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));  
+-------------+-----------------------------------+  
| LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |  
+-------------+-----------------------------------+  
| New York    | new york                          |  
+-------------+-----------------------------------+

For collations of Unicode character sets,LOWER() andUPPER() work according to the Unicode Collation Algorithm (UCA) version in the collation name, if there is one, and UCA 4.0.0 if no version is specified. For example, utf8_unicode_520_ci works according to UCA 5.2.0, whereasutf8_unicode_ci works according to UCA 4.0.0. See Section 10.10.1, “Unicode Character Sets”.
This function is multibyte safe.
In previous versions of MySQL, LOWER() used within a view was rewritten asLCASE() when storing the view's definition. In MySQL 5.7,LOWER() is never rewritten in such cases, but LCASE() used within views is instead rewritten as LOWER(). (Bug #12844279)

mysql> SELECT LPAD('hi',4,'??');  
        -> '??hi'  
mysql> SELECT LPAD('hi',1,'??');  
        -> 'h'  
mysql> SELECT LTRIM('  barbar');  
        -> 'barbar'

This function is multibyte safe.

mysql> SELECT MAKE_SET(1,'a','b','c');  
        -> 'a'  
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');  
        -> 'hello,world'  
mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');  
        -> 'hello'  
mysql> SELECT MAKE_SET(0,'a','b','c');  
        -> ''  
mysql> SELECT OCT(12);  
        -> '14'  
  (1st byte code)  
+ (2nd byte code * 256)  
+ (3rd byte code * 256^2) ...

If the leftmost character is not a multibyte character,ORD() returns the same value as the ASCII() function. 

mysql> SELECT ORD('2');  
        -> 50  
mysql> SELECT QUOTE('Don\'t!');  
        -> 'Don\'t!'  
mysql> SELECT QUOTE(NULL);  
        -> NULL

For comparison, see the quoting rules for literal strings and within the C API in Section 9.1.1, “String Literals”, andmysql_real_escape_string_quote().

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

This function is multibyte safe.

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

This function is multibyte safe.

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

This function is multibyte safe.

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

This function is multibyte safe.

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

This function is multibyte safe.

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

Note
This function implements the original Soundex algorithm, not the more popular enhanced version (also described by D. Knuth). The difference is that original version discards vowels first and duplicates second, whereas the enhanced version discards duplicates first and vowels second.

mysql> SELECT SPACE(6);  
        -> '      '  
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.
If len is less than 1, the result is the empty string.

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.

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 byTO_BASE64() andFROM_BASE64(): 

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.

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, the result is NULL: 

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

A NULL result can occur if the argument toUNHEX() is aBINARY column, because values are padded with 0x00 bytes when stored but those bytes are not stripped on retrieval. For example,'41' is stored into aCHAR(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 aBINARY(3) column as'41\0' and retrieved as'41\0' (with the trailing pad0x00 byte not stripped).'\0' is not a legal hexadecimal digit, soUNHEX() for the column value returns NULL.
For a numeric argument N, the inverse ofHEX(N) is not performed by UNHEX(). UseCONV(HEX(N),16,10) instead. See the description ofHEX().
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”.

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

See the description of LOWER() for information that also applies toUPPER(). 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.
In previous versions of MySQL, UPPER() used within a view was rewritten asUCASE() when storing the view's definition. In MySQL 5.7,UPPER() is never rewritten in such cases, but UCASE() used within views is instead rewritten as UPPER(). (Bug #12844279)

mysql> SET @s = _latin1 'AB' COLLATE latin1_swedish_ci;  
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));  
+------+---------+------------------------+  
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |  
+------+---------+------------------------+  
| AB   | 4142    | 4142                   |  
+------+---------+------------------------+  
mysql> SET @s = _latin1 'ab' COLLATE latin1_swedish_ci;  
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));  
+------+---------+------------------------+  
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |  
+------+---------+------------------------+  
| ab   | 6162    | 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   | 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 useHEX() to display theWEIGHT_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 utf8) COLLATE utf8_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 forVARBINARY, 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: 

mysql> SET NAMES 'latin1';  
mysql> SELECT HEX(WEIGHT_STRING('ab' AS CHAR(4)));  
+-------------------------------------+  
| HEX(WEIGHT_STRING('ab' AS CHAR(4))) |  
+-------------------------------------+  
| 41422020                            |  
+-------------------------------------+  
mysql> SET NAMES 'utf8';  
mysql> SELECT HEX(WEIGHT_STRING('ab' AS CHAR(4)));  
+-------------------------------------+  
| HEX(WEIGHT_STRING('ab' AS CHAR(4))) |  
+-------------------------------------+  
| 0041004200200020                    |  
+-------------------------------------+  
mysql> SELECT HEX(WEIGHT_STRING('ab' AS BINARY(4)));  
+---------------------------------------+  
| HEX(WEIGHT_STRING('ab' AS BINARY(4))) |  
+---------------------------------------+  
| 61620000                              |  
+---------------------------------------+

The LEVEL clause may be given to specify that the return value should contain weights for specific collation levels.
The levels specifier following theLEVEL keyword may be given either as a list of one or more integers separated by commas, or as a range of two integers separated by a dash. Whitespace around the punctuation characters does not matter.
Examples: 

LEVEL 1  
LEVEL 2, 3, 5  
LEVEL 1-3

Any level less than 1 is treated as 1. Any level greater than the maximum for the input string collation is treated as maximum for the collation. The maximum varies per collation, but is never greater than 6.
In a list of levels, levels must be given in increasing order. In a range of levels, if the second number is less than the first, it is treated as the first number (for example, 4-2 is the same as 4-4).
If the LEVEL clause is omitted, MySQL assumes LEVEL 1 -_`max`_, where_max_ is the maximum level for the collation.
If LEVEL is specified using list syntax (not range syntax), any level number can be followed by these modifiers: 

mysql> SELECT HEX(WEIGHT_STRING(0x007fff LEVEL 1));  
+--------------------------------------+  
| HEX(WEIGHT_STRING(0x007fff LEVEL 1)) |  
+--------------------------------------+  
| 007FFF                               |  
+--------------------------------------+  
mysql> SELECT HEX(WEIGHT_STRING(0x007fff LEVEL 1 DESC));  
+-------------------------------------------+  
| HEX(WEIGHT_STRING(0x007fff LEVEL 1 DESC)) |  
+-------------------------------------------+  
| FF8000                                    |  
+-------------------------------------------+  
mysql> SELECT HEX(WEIGHT_STRING(0x007fff LEVEL 1 REVERSE));  
+----------------------------------------------+  
| HEX(WEIGHT_STRING(0x007fff LEVEL 1 REVERSE)) |  
+----------------------------------------------+  
| FF7F00                                       |  
+----------------------------------------------+  
mysql> SELECT HEX(WEIGHT_STRING(0x007fff LEVEL 1 DESC REVERSE));  
+---------------------------------------------------+  
| HEX(WEIGHT_STRING(0x007fff LEVEL 1 DESC REVERSE)) |  
+---------------------------------------------------+  
| 0080FF                                            |  
+---------------------------------------------------+

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, seeSection 4.5.1, “mysql — The MySQL Command-Line Client”.