Working with Strings
ASCII(str string)
BIN(int integer)
CHAR(int integer, ...)
CHARACTER_LENGTH(str string)
CHAR_LENGTH(str string)
CONCAT(str1 string, str2 string,...)
CONCAT_WS(separator string, str1 string, str2 string,...)
CONV(int_or_str integer or string, from_base integer, to_base integer)
ELT( N integer, str1 string, str2 string, str3 string,...)
EXPORT_SET( bits, on string , off string, [separator char, [number_of_bits integer]])
FIELD( str string, str1 string, str2 string, str3 string,...)
FIND_IN_SET( str string, strlist string )
HEX(int_or_str string or integer)
INSERT( str string, pos integer, len integer, newstr string )
INSTR( str string, substr string)
LCASE( str string ) string
LEFT( str string, len integer)
LENGTH(str string)
LOAD_FILE( file_name string )
LOCATE(substr string, str string, pos integer)
LOWER( str string )
LPAD( str string, len integer, padstr string)
LTRIM( str string )
MAKE_SET( bits, str1 string , str2 string ,...)
MID(str string, pos integer, len integer)
OCT(int integer)
OCTET_LENGTH(str string)
LOCATE(substr string, str string)
ORD(str string)
POSITION(substr string IN str string)
REPLACE( str string, from_str string, to_str string)
REPEAT( str string, count integer)
REVERSE( str string )
RIGHT( str string, len integer)
RPAD( str string, len integer, padstr string)
RTRIM( str string )
SOUNDEX( str string )
SPACE( N integer )
SUBSTRING(str string FROM pos integer FOR len integer)
SUBSTRING_INDEX( str string, delim string, count integer)
TRIM( [[BOTH | LEADING | TRAILING] [remstr] string FROM] str string )
UCASE( str string )
UPPER( str string )
Any string function will return NULL if the resulting string exceeds the length set by the server parameter max_allowed_packet.
Returns the ASCII code of the leftmost character of the string str. Returns 0 if the string str is empty. Returns NULL if the string str does not exist.
Examples:
mysql> select ASCII('2');
-> 50
mysql> select ASCII(2);
-> 50
mysql> select ASCII('dx');
-> 100
See also ORD().
If the leftmost character of the string str is multibyte, returns its code in the format: ((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...].
If the leftmost character of the string str is not multibyte, the behavior is similar to the ASCII() function - it returns the ASCII code of the leftmost character of the string str.
Examples:
mysql> select ORD('2');
-> 50
See also ASCII().
string CONV(int_or_str integer or string, from_base integer, to_base integer)
A powerful function for converting a number (int_or_str) from one number system (from_base) to another (to_base). Returns NULL if the arguments are NULL. The minimum value for .._base is 2, and the maximum is 36.
Examples:
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'
Returns the binary value of the argument int. Equivalent to CONV(int,10,2). Returns NULL if the argument int is NULL.
Examples:
mysql> select BIN(12);
-> '1100'
See also CONV().
Returns the octal value of the argument int. Equivalent to CONV(int,10,8). Returns NULL if the argument int is NULL.
Examples:
mysql> select OCT(12);
-> '14'
See also CONV().
string HEX(int_or_str string or integer)
Returns the hexadecimal value of the argument int_or_string. Equivalent to CONV(int,10,16). Returns NULL if the argument int_or_string is NULL.
Examples:
mysql> select HEX(255);
-> 'FF'
mysql> select HEX("abc");
-> 616263
mysql> select 0x616263;
-> "abc"
See also CONV().
Returns a string of characters specified by the ASCII code(s). NULL values are skipped.
Examples:
mysql> select CHAR(77,121,83,81,'76');
-> 'MySQL'
mysql> select CHAR(77,77.3,'77.3');
-> 'MMM'
string CONCAT(str1 string, str2 string,...)
Concatenates strings. The result is a string consisting of the arguments. Returns NULL if any of the arguments is NULL. More than 2 arguments are allowed. Numeric arguments are converted to their corresponding strings.
Examples:
mysql> select CONCAT('My', 'S', 'QL');
-> 'MySQL'
mysql> select CONCAT('My', NULL, 'QL');
-> NULL
mysql> select CONCAT(14.3);
-> '14.3'
See also CONCAT_WS().
string CONCAT_WS(separator string, str1 string, str2 string,...)
Concatenates strings similar to the CONCAT() function, but with a separator inserted between the argument strings. If the separator is NULL, the result will be NULL. The function skips other arguments if their value is NULL.
Examples:
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'
See also CONCAT().
The function returns the length of the string str.
Examples:
mysql> select LENGTH('text');
-> 4
Note: multi-byte characters are counted by the CHAR_LENGTH() function.
See also OCTET_LENGTH(), CHAR_LENGTH(), CHARACTER_LENGTH().
integer OCTET_LENGTH(str string)
The function returns the length of the string str.
Examples:
mysql> select OCTET_LENGTH('text');
-> 4
Note: multi-byte characters are counted by the CHAR_LENGTH() function.
See also LENGTH(), CHAR_LENGTH(), CHARACTER_LENGTH().
integer CHAR_LENGTH(str string)
The function returns the length of the string str. Supports multi-byte characters.
Examples:
mysql> select CHAR_LENGTH('text');
-> 4
See also LENGTH(), OCTET_LENGTH(), CHARACTER_LENGTH().
integer CHARACTER_LENGTH(str string)
The function returns the length of the string str.
Examples:
mysql> select CHARACTER_LENGTH('text');
-> 4
Note: multi-byte characters are counted by the CHAR_LENGTH() function.
See also LENGTH(), OCTET_LENGTH(), CHAR_LENGTH().
integer POSITION(substr string IN str string)
Returns the position of the first occurrence of the substring substr in the string str. Returns 0 if substr is not found in the string str. Supports multi-byte characters.
Examples:
mysql> select POSITION('bar', 'foobarbar');
-> 4
mysql> select POSITION('xbar', 'foobar');
-> 0
See also INSTR().
integer LOCATE(substr string, str string, pos integer)
LOCATE(substr string, str string) returns the position of the first occurrence of the substring substr in the string str. Returns 0 if substr is not found in str. LOCATE(substr string, str string, pos integer) returns the position of the first occurrence of the substring substr in the string str, starting from position pos. Returns 0 if substr is not found in str (taking the position into account, of course). Supports multibyte characters.
Examples:
mysql> select LOCATE('bar', 'foobarbar',5);
-> 7
mysql> select LOCATE('bar', 'foobarbar');
-> 4
mysql> select LOCATE('xbar', 'foobar');
-> 0
See also INSTR(), POSITION().
integer INSTR( str string, substr string)
Returns the position of the first occurrence of the substring substr in the string str. Returns 0 if substr is not found in str (taking the position into account, of course). Supports multibyte characters. Analogous to LOCATE(), but with the arguments swapped.
Examples:
mysql> select INSTR('foobarbar', 'bar');
-> 4
mysql> select INSTR('xbar', 'foobar');
-> 0
See also LOCATE(), POSITION().
string LPAD( str string, len integer, padstr string)
Returns the string str with the string padstr prepended to it, such that the total number of characters in the returned string is len. If len is greater than the sum of the lengths of the strings, the difference is made up by repeating the padstr.
Examples:
mysql> select LPAD('hi',4,'??');
-> '??hi'
See also RPAD().
string RPAD( str string, len integer, padstr string)
Returns the string str with the string padstr appended to it, such that the total number of characters in the returned string is len. If len is greater than the sum of the lengths of the strings, the difference is made up by repeating the padstr.
Examples:
mysql> select RPAD('hi',5,'?');
-> 'hi???'
See also LPAD().
string LEFT( str string, len integer)
Returns the first len characters from the string str. Supports multibyte characters.
Examples:
mysql> select LEFT('foobarbar', 5);
-> 'fooba'
See also RIGHT(), SUBSTRING(), MID(), SUBSTRING_INDEX()
string RIGHT( str string, len integer)
Returns the last len characters from the string str. Supports multibyte characters.
Examples:
mysql> select RIGHT('foobarbar', 4);
-> 'rbar'
See also LEFT(), SUBSTRING(), MID(), SUBSTRING_INDEX().
string SUBSTRING( str string, pos integer, len integer )
string SUBSTRING(str string FROM pos integer FOR len integer)
Returns a substring of string str with length len characters starting at position pos. Supports multibyte characters. The variant with "FROM" is used in ANSI SQL92 syntax. In the SUBSTRING() function, if the len argument is not specified, it returns the entire substring of string str starting from position pos.
Examples:
mysql> select SUBSTRING('Quadratically',5,6);
-> 'ratica'
mysql> select SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> select SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
See also LEFT(), RIGHT(), SUBSTRING_INDEX().
string MID(str string, pos integer, len integer)
Returns a substring of string str with length len characters starting at position pos. Supports multibyte characters. The variant with "FROM" is used in ANSI SQL92 syntax. In the SUBSTRING() function, if the len argument is not specified, it returns the entire substring of string str starting from position pos.
Examples:
mysql> select MID('Quadratically',5,6);
-> 'ratica'
mysql> select MID('Quadratically',5);
-> 'ratically'
mysql> select MID('foobarbar' FROM 4);
-> 'barbar'
See also LEFT(), RIGHT(), SUBSTRING_INDEX().
string SUBSTRING_INDEX(str string, delim string, count integer)
Returns the entire substring of string str up to the delimiter delim found at the count-th occurrence.
Examples:
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
See also LEFT(), SUBSTRING(), MID(), RIGHT().
Returns string str with leading spaces removed. Supports multibyte characters.
Examples:
mysql> select LTRIM(' barbar');
-> 'barbar'
Returns string str with trailing spaces removed. Supports multibyte characters.
Examples:
mysql> select RTRIM('barbar ');
-> 'barbar'
string TRIM([[BOTH | LEADING | TRAILING] [remstr] string FROM] str string)
With one argument "str", it returns string str with trailing spaces removed. The "remstr" argument specifies the character or substring to remove. Supports multibyte characters. mysql> select TRIM(' bar '); -> 'bar'
Examples:
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'
Computes the soundex key for str. The soundex key has the property that words pronounced similarly have the same soundex key, which can be used to correct search results. Returns a 4-character string starting with a letter A-Z.
Examples:
mysql> select SOUNDEX('Hello');
-> 'H400'
mysql> select SOUNDEX('Quadratically');
-> 'Q36324'
Returns a string consisting of N spaces.
Examples:
mysql> select SPACE(6);
-> ' '
See also REPEAT().
string REPLACE( str string, from_str string, to_str string)
Returns the string str with all occurrences of from_str replaced by to_str. Supports multibyte characters.
Examples:
mysql> select REPLACE('www.websupport.maryan.com.ua', 'w', 'Ww');
-> 'WwWwWw.Wwebsupport.maryan.com.ua'
string REPEAT( str string, count integer)
Returns a string consisting of str repeated count times. Returns NULL if any argument is NULL.
Examples:
mysql> select REPEAT('MySQL', 3);
-> 'MySQLMySQLMySQL'
See also SPACE().
Returns a string with all characters in reverse order. See example. Supports multibyte characters.
Examples:
mysql> select REVERSE('abc');
-> 'cba'
string INSERT( str string, pos integer, len integer, newstr string )
Returns the string str with the substring newstr inserted at position pos of str, replacing len characters. Supports multibyte characters.
Examples:
mysql> select INSERT('Quadratic', 3, 4, 'What');
-> 'QuWhattic'
string ELT( N integer, str1 string, str2 string, str3 string,...)
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.
Examples:
mysql> select ELT(1, 'ej', 'Heja', 'hej', 'foo');
-> 'ej'
mysql> select ELT(4, 'ej', 'Heja', 'hej', 'foo');
-> 'foo'
See also FIELD().
integer FIELD( str string, str1 string, str2 string, str3 string,...)
Returns the index (position) of str in the list of strings str1, str2, str3, ... . Returns 0 if str is not found in the list.
Examples:
mysql> select FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
-> 2
mysql> select FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
-> 0
See also ELT().
integer FIND_IN_SET( str string, strlist string )
Returns a value from 1 to ..., depending on the position of str among the strings (substrings) in strlist. Strings (substrings) in strlist are separated by ",". Returns 0 if str is not found among the strings in strlist. Returns NULL if any argument is NULL.
Examples:
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
See also MAKE_SET().
string MAKE_SET( bits, str1 string , str2 string ,...)
Returns one of the argument strings depending on the bitwise state of bits. str1 is returned for bit 0, str2 for bit 1, and so on.
Examples:
mysql> SELECT MAKE_SET(1,'a','b','c');
-> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
-> 'hello,world'
mysql> SELECT MAKE_SET(0,'a','b','c');
-> ''
See also ELT(), FIND_IN_SET().
string EXPORT_SET( bits, on string , off string, [separator char, [number_of_bits integer]])
Returns a string in which for each bit the state on or off is displayed. States are separated by separator.
Examples:
mysql> select EXPORT_SET(5,'Y','N',',',4)
-> Y,N,Y,N
string LCASE( str string ) string
Returns the string str with all uppercase letters replaced by lowercase. Supports multibyte characters. (Default ISO-8859-1 Latin1).
Examples:
mysql> select LCASE('QUADRATICALLY');
-> 'quadratically'
See also UCASE(), UPPER(), LCASE(), LOWER().
Returns the string str with all uppercase letters replaced by lowercase. Supports multibyte characters. (Default ISO-8859-1 Latin1).
Examples:
mysql> select LOWER('QUADRATICALLY');
-> 'quadratically'
See also UCASE(), UPPER(), LCASE(), LOWER().
Returns the string str with all lowercase letters replaced by uppercase. Supports multibyte characters. (Default ISO-8859-1 Latin1).
Examples:
mysql> select UCASE('Hej');
-> 'HEJ'
See also UCASE(), UPPER(), LCASE(), LOWER().
Returns the string str with all lowercase letters replaced by uppercase. Supports multibyte characters. (Default ISO-8859-1 Latin1).
Examples:
mysql> select UPPER('Hej');
-> 'HEJ'
See also UCASE(), UPPER(), LCASE(), LOWER().
string LOAD_FILE( file_name string )
Reads a file and returns its content as a string. file_name is the full path to the file on the server. You must ensure the physical existence of the file and that the script has the appropriate access rights to it. The file size must not exceed the value set by the max_allowed_packet parameter. In case of problems (e.g., file not found), it returns NULL.