(mysql.info) string-functions
Info Catalog
(mysql.info) control-flow-functions
(mysql.info) functions
(mysql.info) numeric-functions
12.3 String Functions
=====================
Menu
* string-comparison-functions String Comparison Functions
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 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
* `CHAR(N,... [USING CHARSET_NAME])'
`CHAR()' interprets each argument N as an integer 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'
As of MySQL 5.0.15, `CHAR()' arguments larger than 255 are
converted into multiple result bytes. For example, `CHAR(256)' is
equivalent to `CHAR(1,0)', and `CHAR(256*256)' is equivalent to
`CHAR(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(0x65)), CHARSET(CHAR(0x65 USING utf8));
+---------------------+--------------------------------+
| CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 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 from `CHAR()' becomes `NULL'.
Before MySQL 5.0.15, `CHAR()' returns a string in the connection
character set and the `USING' clause is unavailable. In addition,
each argument is interpreted modulo 256, so `CHAR(256)' and
`CHAR(256*256)' both are equivalent to `CHAR(0)'.
* `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()'.
* `CONCAT(STR1,STR2,...)'
Returns the string that results from concatenating the arguments.
May have one or more arguments. If all arguments are non-binary
strings, the result is a non-binary string. If the arguments
include any binary strings, the result is a binary string. A
numeric argument is converted to its equivalent binary string
form; if you want to avoid that, you can use an explicit type
cast, as in this example:
SELECT CONCAT(CAST(INT_COL AS CHAR), CHAR_COL);
`CONCAT()' returns `NULL' if any argument is `NULL'.
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 Concatenate 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'.
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.
* `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 such that 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 (the default
being the comma character ‘`,'’). The number of bits examined
is given by NUMBER_OF_BITS (defaults to 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 (position) of STR in the STR1, STR2, STR3, `...'
list. Returns `0' if STR is not found.
If all arguments to `FIELD()' are strings, all arguments are
compared as strings. If all arguments are numbers, they are
compared as numbers. Otherwise, the arguments are compared as
double.
If STR is `NULL', the return value is `0' because `NULL' fails
equality comparison with any value. `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 in the range of 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 does not work properly if the first argument
contains a comma (‘`,'’) character.
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
* `FORMAT(X,D)'
Formats the number X to a format like `'#,###,###.##'', rounded to
D decimal places, and returns the result as a string. If D is `0',
the result has no decimal point or fractional part.
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'
* `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)'.
If N_OR_S is a string, returns a hexadecimal string representation
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. Returns
the original string if POS is not within the length of the string.
Replaces the rest of the string from position POS is LEN is not
within the length of the rest of the string. Returns `NULL' if any
argument is `NULL'.
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 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 order of the arguments is reversed.
mysql> SELECT INSTR('foobarbar', 'bar');
-> 4
mysql> SELECT INSTR('xbar', 'foobar');
-> 0
This function is multi-byte safe, and is case sensitive only if at
least one 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. To use
this function, the file must be located on the server host, you
must specify the full pathname to the file, and you must have the
`FILE' privilege. The file must be readable by all and its size
less than `max_allowed_packet' bytes.
If the file does not exist or cannot be read because one of the
preceding conditions is not satisfied, the function returns `NULL'.
As of MySQL 5.0.19, the `character_set_filesystem' system variable
controls interpretation of filenames that are given as literal
strings.
mysql> UPDATE t
SET blob_col=LOAD_FILE('/tmp/picture')
WHERE id=1;
* `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, and is case-sensitive only if at
least one 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
`latin1' (cp1252 West European).
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 × 2562) ...
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
enclosed 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 enclosing single quotes.
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 is less than 1, 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. `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 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 in STR are ignored. All international alphabetic
characters outside the A-Z range are treated as vowels.
mysql> SELECT SOUNDEX('Hello');
-> 'H400'
mysql> SELECT SOUNDEX('Quadratically');
-> 'Q36324'
* 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.
* `EXPR1 SOUNDS LIKE EXPR2'
This is the same as `SOUNDEX(EXPR1) = SOUNDEX(EXPR2)'.
* `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. 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.
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 multi-byte safe.
If LEN is less than 1, the result is the empty string.
`SUBSTR()' is a synonym for `SUBSTRING()'.
* `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 multi-byte safe.
* `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 multi-byte safe.
* `UCASE(STR)'
`UCASE()' is a synonym for `UPPER()'.
* `UNHEX(STR)'
Performs the inverse operation 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'
* `UPPER(STR)'
Returns the string STR with all characters changed to uppercase
according to the current character set mapping. The default is
`latin1' (cp1252 West European).
mysql> SELECT UPPER('Hej');
-> 'HEJ'
This function is multi-byte safe.
Info Catalog
(mysql.info) control-flow-functions
(mysql.info) functions
(mysql.info) numeric-functions
automatically generated byinfo2html