(mysql.info.gz) String functions
Info Catalog
(mysql.info.gz) Control flow functions
(mysql.info.gz) Functions
(mysql.info.gz) Numeric Functions
12.3 String 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.
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. 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.
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. 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 `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.
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 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'
* 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.
Menu
* String comparison functions String Comparison Functions
Info Catalog
(mysql.info.gz) Control flow functions
(mysql.info.gz) Functions
(mysql.info.gz) Numeric Functions
automatically generated byinfo2html