String Functions

String Functions
Prev	Chapter 13. Functions and Operators	Next

String-valued functions return NULL if the length of the result would be greater than the value of the max_allowed_packet system variable. See the section called “Tuning Server Parameters”.

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

ASCII(str)

Returns the numeric value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL. ASCII() works for characters with numeric values from 0 to 255.

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

See also the ORD() function.

BIN(N)

Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,2). Returns NULL if N is NULL.

mysql> SELECT BIN(12);
        -> '1100'

BIT_LENGTH(str)

Returns the length of the string str in bits.

mysql> SELECT BIT_LENGTH('text');
        -> 32

BIT_LENGTH() was added in MySQL 4.0.2.

CHAR(N,...)

CHAR() interprets the arguments as integers and returns a string consisting of the characters given by the code values of those integers. NULL values are skipped.

mysql> SELECT CHAR(77,121,83,81,'76');
        -> 'MySQL'
mysql> SELECT CHAR(77,77.3,'77.3');
        -> 'MMM'

CHAR_LENGTH(str)

Returns the length of the string str, measured in characters. A multi-byte character counts as a single character. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

CHARACTER_LENGTH(str)

CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().

COMPRESS(string_to_compress)

Compresses a string. This function requires MySQL to have been compiled with a compression library such as zlib. Otherwise, the return value is always NULL. The compressed string can be uncompressed with UNCOMPRESS().

mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000)));
        -> 21
mysql> SELECT LENGTH(COMPRESS(''));
        -> 0
mysql> SELECT LENGTH(COMPRESS('a'));
        -> 13
mysql> SELECT LENGTH(COMPRESS(REPEAT('a',16)));
        -> 15

The compressed string contents are stored the following way:

Empty strings are stored as empty strings.
Non-empty strings are stored as a four-byte length of the uncompressed string (low byte first), followed by the compressed string. If the string ends with space, an extra ‘.’ character is added to avoid problems with endspace trimming should the result be stored in a CHAR or VARCHAR column. (Use of CHAR or VARCHAR to store compressed strings is not recommended. It is better to use a BLOB column instead.)

COMPRESS() was added in MySQL 4.1.1.

CONCAT(str1,str2,...)

Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL. May have one or more arguments. A numeric argument is converted to its equivalent string form.

mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'

CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string as can the rest of the arguments. If the separator is NULL, the result is NULL. The function skips any NULL values after the separator argument.

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'

Before MySQL 4.0.14, CONCAT_WS() skips empty strings as well as NULL values.

CONV(N,from_base,to_base)

Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV() works with 64-bit precision.

mysql> SELECT CONV('a',16,2);
        -> '1010'
mysql> SELECT CONV('6E',18,8);
        -> '172'
mysql> SELECT CONV(-17,10,-18);
        -> '-H'
mysql> SELECT CONV(10+'10'+'10'+0xa,10,10);
        -> '40'

ELT(N,str1,str2,str3,...)

Returns str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD().

mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
        -> 'ej'
mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
        -> 'foo'

EXPORT_SET(bits,on,off[,separator[,number_of_bits]])

Returns a string in which for every bit set in the value bits, you get an on string and for every reset bit you get an off string. Bits in bits are examined from right to left (from low-order to high-order bits). Strings are added to the result from left to right, separated by the separator string (default ‘,’). The number of bits examined is given by number_of_bits (default 64).

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'

FIELD(str,str1,str2,str3,...)

Returns the index of str in the str1, str2, str3, ... list. Returns 0 if str is not found. FIELD() is the complement of ELT().

mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
        -> 2
mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
        -> 0

FIND_IN_SET(str,strlist)

Returns a value 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by ‘,’ characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function will not work properly if the first argument contains a comma (‘,’) character.

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
        -> 2

HEX(N_or_S)

If N_OR_S is a number, returns a string representation of the hexadecimal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,16).

From MySQL 4.0.1 and up, if N_OR_S is a string, returns a hexadecimal string of N_OR_S where each character in N_OR_S is converted to two hexadecimal digits.

mysql> SELECT HEX(255);
        -> 'FF'
mysql> SELECT 0x616263;
        -> 'abc'
mysql> SELECT HEX('abc');
        -> 616263

INSERT(str,pos,len,newstr)

Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr.

mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
        -> 'QuWhattic'

This function is multi-byte safe.

INSTR(str,substr)

Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the arguments are swapped.

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

This function is multi-byte safe. In MySQL 3.23, this function is case sensitive. For 4.0 on, it is case sensitive only if either argument is a binary string.

LCASE(str)

LCASE() is a synonym for LOWER().

LEFT(str,len)

Returns the leftmost len characters from the string str.

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

LENGTH(str)

Returns the length of the string str, measured in bytes. A multi-byte character counts as multiple bytes. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

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

LOAD_FILE(file_name)

Reads the file and returns the file contents as a string. The file must be located on the server, you must specify the full pathname to the file, and you must have the FILE privilege. The file must be readable by all and be smaller than max_allowed_packet bytes.

If the file doesn't exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

mysql> UPDATE tbl_name
           SET blob_column=LOAD_FILE('/tmp/picture')
           WHERE id=1;

Before MySQL 3.23, you must read the file inside your application and create an INSERT statement to update the database with the file contents. If you are using the MySQL++ library, one way to do this can be found in the MySQL++ manual, available at http://dev.mysql.com/doc/.

LOCATE(substr,str), LOCATE(substr,str,pos),

The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str.

mysql> SELECT LOCATE('bar', 'foobarbar');
        -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0
mysql> SELECT LOCATE('bar', 'foobarbar',5);
        -> 7

This function is multi-byte safe. In MySQL 3.23, this function is case sensitive. For 4.0 on, it is case sensitive only if either argument is a binary string.

LOWER(str)

Returns the string str with all characters changed to lowercase according to the current character set mapping (the default is ISO-8859-1 Latin1).

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

This function is multi-byte safe.

LPAD(str,len,padstr)

Returns the string str, left-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.

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

LTRIM(str)

Returns the string str with leading space characters removed.

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

This function is multi-byte safe.

MAKE_SET(bits,str1,str2,...)

Returns a set value (a string containing substrings separated by ‘,’ characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, and so on. NULL values in str1, str2, ... are not appended to the result.

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');
        -> ''

MID(str,pos,len)

MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).

OCT(N)

Returns a string representation of the octal value of N, where N is a longlong (BIGINT)number. This is equivalent to CONV(N,10,8). Returns NULL if N is NULL.

mysql> SELECT OCT(12);
        -> '14'

OCTET_LENGTH(str)

OCTET_LENGTH() is a synonym for LENGTH().

ORD(str)

If the leftmost character of the string str is a multi-byte character, returns the code for that character, calculated from the numeric values of its constituent bytes using this formula:

  (1st byte code)
+ (2nd byte code * 256)
+ (3rd byte code * 256^2) ...

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

mysql> SELECT ORD('2');
        -> 50

POSITION(substr IN str)

POSITION(substr IN str) is a synonym for LOCATE(substr,str).

QUOTE(str)

Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned surrounded by single quotes and with each instance of single quote (‘'’), backslash (‘\’), ASCII NUL, and Control-Z preceded by a backslash. If the argument is NULL, the return value is the word “NULL” without surrounding single quotes. The QUOTE() function was added in MySQL 4.0.3.

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

REPEAT(str,count)

Returns a string consisting of the string str repeated count times. If count <= 0, returns an empty string. Returns NULL if str or count are NULL.

mysql> SELECT REPEAT('MySQL', 3);
        -> 'MySQLMySQLMySQL'

REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str.

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

This function is multi-byte safe.

REVERSE(str)

Returns the string str with the order of the characters reversed.

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

This function is multi-byte safe.

RIGHT(str,len)

Returns the rightmost len characters from the string str.

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

This function is multi-byte 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.

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

This function is multi-byte safe.

RTRIM(str)

Returns the string str with trailing space characters removed.

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

This function is multi-byte safe.

SOUNDEX(str)

Returns a soundex string from str. 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 non-alphabetic characters are ignored in the given string. All international alphabetic characters outside the A-Z range are treated as vowels.

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 then duplicates, whereas the enhanced version discards duplicates first and then vowels.

expr1 SOUNDS LIKE expr2

This is the same as SOUNDEX(expr1) = SOUNDEX(expr2). It is available only in MySQL 4.1 or later.

SPACE(N)

Returns a string consisting of N space characters.

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

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.

mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'

This function is multi-byte safe.

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.

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

This function is multi-byte safe.

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM(remstr FROM] str),

Returns the string str with all remstr prefixes and/or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. If remstr is optional and 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 multi-byte safe.

UCASE(str)

UCASE() is a synonym for UPPER().

UNCOMPRESS(string_to_uncompress)

Uncompresses a string compressed by the COMPRESS() function. If the argument is not a compressed value, the result is NULL. This function requires MySQL to have been compiled with a compression library such as zlib. Otherwise, the return value is always NULL.

mysql> SELECT UNCOMPRESS(COMPRESS('any string'));
        -> 'any string'
mysql> SELECT UNCOMPRESS('any string');
        -> NULL

UNCOMPRESS() was added in MySQL 4.1.1.

UNCOMPRESSED_LENGTH(compressed_string)

Returns the length of a compressed string before compression.

mysql> SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30)));
        -> 30

UNCOMPRESSED_LENGTH() was added in MySQL 4.1.1.

UNHEX(str)

Does the opposite of HEX(str). That is, it interprets each pair of hexadecimal digits in the argument as a number and converts it to the character represented by the number. The resulting characters are returned as a binary string.

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

UNHEX() was added in MySQL 4.1.2.

UPPER(str)

Returns the string str with all characters changed to uppercase according to the current character set mapping (the default is ISO-8859-1 Latin1).

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

This function is multi-byte safe.

String Comparison Functions

MySQL automatically converts numbers to strings as necessary, and vice versa.

mysql> SELECT 1+'1';
        -> 2
mysql> SELECT CONCAT(2,' test');
        -> '2 test'

If you want to convert a number to a string explicitly, use the CAST() or CONCAT() function:

mysql> SELECT 38.8, CAST(38.8 AS CHAR);
        -> 38.8, '38.8'
mysql> SELECT 38.8, CONCAT(38.8);
        -> 38.8, '38.8'

CAST() is preferable, but it is unavailable before MySQL 4.0.2.

If a string function is given a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This affects only comparisons.

Normally, if any expression in a string comparison is case sensitive, the comparison is performed in case-sensitive fashion.

expr LIKE pat [ESCAPE 'escape-char']

Pattern matching using SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.

With LIKE you can use the following two wildcard characters in the pattern:

Character	Description
`%`	Matches any number of characters, even zero characters
`_`	Matches exactly one character

mysql> SELECT 'David!' LIKE 'David_';
        -> 1
mysql> SELECT 'David!' LIKE '%D%v%';
        -> 1

To test for literal instances of a wildcard character, precede the character with the escape character. If you don't specify the ESCAPE character, ‘\’ is assumed.

String	Description
`\%`	Matches one ‘`%`’ character
`\_`	Matches one ‘`_`’ character

mysql> SELECT 'David!' LIKE 'David\_';
        -> 0
mysql> SELECT 'David_' LIKE 'David\_';
        -> 1

To specify a different escape character, use the ESCAPE clause:

mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
        -> 1

The following two statements illustrate that string comparisons are not case sensitive unless one of the operands is a binary string:

mysql> SELECT 'abc' LIKE 'ABC';
        -> 1
mysql> SELECT 'abc' LIKE BINARY 'ABC';
        -> 0

In MySQL, LIKE is allowed on numeric expressions. (This is an extension to the standard SQL LIKE.)

mysql> SELECT 10 LIKE '1%';
        -> 1

Note: Because MySQL uses the C escape syntax in strings (for example, ‘\n’ to represent newline), you must double any ‘\’ that you use in your LIKE strings. For example, to search for ‘\n’, specify it as ‘\\n’. To search for ‘\’, specify it as ‘\\\\’ (the backslashes are stripped once by the parser and another time when the pattern match is done, leaving a single backslash to be matched).

expr NOT LIKE pat [ESCAPE 'escape-char']

This is the same as NOT (expr LIKE pat [ESCAPE 'escape-char']).

expr NOT REGEXP pat, expr NOT RLIKE pat,

This is the same as NOT (expr REGEXP pat).

expr REGEXP pat, expr RLIKE pat,

Performs a pattern match of a string expression expr against a pattern pat. The pattern can be an extended regular expression. The syntax for regular expressions is discussed in Appendix F, MySQL Regular Expressions. Returns 1 if expr matches pat, otherwise returns 0. If either expr or pat is NULL, the result is NULL. RLIKE is a synonym for REGEXP, provided for mSQL compatibility. Note: Because MySQL uses the C escape syntax in strings (for example, ‘\n’ to represent newline), you must double any ‘\’ that you use in your REGEXP strings. As of MySQL 3.23.4, REGEXP is not case sensitive for normal (not binary) strings.

mysql> SELECT 'Monty!' REGEXP 'm%y%%';
        -> 0
mysql> SELECT 'Monty!' REGEXP '.*';
        -> 1
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
        -> 1
mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
        -> 1  0
mysql> SELECT 'a' REGEXP '^[a-d]';
        -> 1

REGEXP and RLIKE use the current character set (ISO-8859-1 Latin1 by default) when deciding the type of a character. However, these operators are not multi-byte safe.

STRCMP(expr1,expr2)

STRCMP() returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise.

mysql> SELECT STRCMP('text', 'text2');
        -> -1
mysql> SELECT STRCMP('text2', 'text');
        -> 1
mysql> SELECT STRCMP('text', 'text');
        -> 0

As of MySQL 4.0, STRCMP() uses the current character set when performing comparisons. This makes the default comparison behavior case insensitive unless one or both of the operands are binary strings. Before MySQL 4.0, STRCMP() is case sensitive.

Prev	Up	Next
Control Flow Functions	Home	Numeric Functions