MySQL string functions
- 2020-05-07 20:35:57
- OfStack
MySQL string functions
for operations against string positions, the first position is marked as 1.
ASCII(str)
Returns the ASCII code value for the leftmost character of the string str. If str is an empty string, return 0. If str is NULL, return NULL.
mysql > select ASCII('2');
- > 50
mysql > select ASCII(2);
- > 50
mysql > select ASCII('dx');
- > 100
Also see the ORD() function.
ORD(str)
If the left-most character of the string str is 1 multi-byte character, pass the format ((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...) Returns the ASCII code value of the character to return the multi-byte character code. If the leftmost character is not a multi-byte character. Returns the same value as the ASCII() function.
mysql > select ORD('2');
- > 50
CONV(N,from_base,to_base)
You switch Numbers between bases. Returns the string number of the number N, transformed from from_base to to_base basis, and NULL if any parameter is NULL. The parameter N is interpreted as 1 integer, but can be specified as 1 integer or 1 string. The smallest base is 2 and the largest base is 36. If to_base is a negative number, N is considered a signed number; otherwise, N is considered an unsigned number. CONV works with 64 point accuracy.
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'
BIN(N)
Returns a string representation of the 2-base value N, where N is a long integer (BIGINT) number, which is equivalent to CONV(N,10,2). If N is NULL, return NULL.
mysql > select BIN(12);
- > '1100'
OCT(N)
Returns a string representation of the 8-base value N, where N is a long integer number, which is equivalent to CONV(N,10,8). If N is NULL, return NULL.
mysql > select OCT(12);
- > '14'
HEX(N)
Returns a representation of the base 106 value N1 string, where N is a long integer (BIGINT) number, which is equivalent to CONV(N,10,16). If N is NULL, return NULL.
mysql > select HEX(255);
- > 'FF'
CHAR(N,...)
CHAR() interprets arguments as integers and returns a string made up of ASCII code characters for these integers. The NULL value is skipped.
mysql > select CHAR(77,121,83,81,'76');
- > 'MySQL'
mysql > select CHAR(77,77.3,'77.3');
- > 'MMM'
CONCAT(str1,str2,...)
Returns a string from the parameter link. If any parameter is NULL, return NULL. You can have more than two arguments. One numeric parameter is converted to an equivalent string.
mysql > select CONCAT('My', 'S', 'QL');
- > 'MySQL'
mysql > select CONCAT('My', NULL, 'QL');
- > NULL
mysql > select CONCAT(14.3);
- > '14.3'
LENGTH(str)
OCTET_LENGTH(str)
CHAR_LENGTH(str)
CHARACTER_LENGTH(str)
Returns the length of the string str.
mysql > select LENGTH('text');
- > 4
mysql > select OCTET_LENGTH('text');
- > 4
Note that for multi-byte characters, its CHAR_LENGTH() is evaluated only once.
LOCATE(substr,str)
POSITION(substr IN str)
Returns the position where the substring substr appears in the first string str, and if substr is not in str, returns 0.
mysql > select LOCATE('bar', 'foobarbar');
- > 4
mysql > select LOCATE('xbar', 'foobar');
- > 0
This function is multi-byte reliable.
LOCATE(substr,str,pos)
Returns the position of the substring substr at the first occurrence of the string str, starting at pos. If substr is not in str, return 0.
mysql > select LOCATE('bar', 'foobarbar',5);
- > 7
This function is multi-byte reliable.
INSTR(str,substr)
Returns the position where the substring substr occurs at the first of the string str. This is the same as LOCATE() with two arguments, except that the arguments are reversed.
mysql > select INSTR('foobarbar', 'bar');
- > 4
mysql > select INSTR('xbar', 'foobar');
- > 0
This function is multi-byte reliable.
LPAD(str,len,padstr)
Returns the string str, filling the left side with the string padstr until str is len characters long.
mysql > select LPAD('hi',4,'??');
- > '??hi'
RPAD(str,len,padstr)
Returns the string str, filling the right side with the string padstr until str is len characters long.
mysql > select RPAD('hi',5,'?');
- > 'hi???'
LEFT(str,len)
Returns the leftmost len character of the string str.
mysql > select LEFT('foobarbar', 5);
- > 'fooba'
This function is multi-byte reliable.
RIGHT(str,len)
Returns the rightmost len character of the string str.
mysql > select RIGHT('foobarbar', 4);
- > 'rbar'
This function is multi-byte reliable.
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)
MID(str,pos,len)
Returns a substring of len characters from the string str, starting at the position pos. The variant form using FROM is the ANSI SQL92 syntax.
mysql > select SUBSTRING('Quadratically',5,6);
- > 'ratica'
This function is multi-byte reliable.
SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
Returns a substring from pos at the beginning of the string str.
mysql > select SUBSTRING('Quadratically',5);
- > 'ratically'
mysql > select SUBSTRING('foobarbar' FROM 4);
- > 'barbar'
This function is multi-byte reliable.
SUBSTRING_INDEX(str,delim,count)
Returns the substring after delim from the count separator of the string str. If count is positive, returns the last separator to all characters to the left (counting from the left). If count is negative, return the last separator to all characters on the right (counting from the right).
mysql > select SUBSTRING_INDEX('www.mysql.com', '.', 2);
- > 'www.mysql'
mysql > select SUBSTRING_INDEX('www.mysql.com', '.', -2);
- > 'mysql.com'
This function is reliable for multiple bytes.
LTRIM(str)
Returns the string str with its leading space character removed.
mysql > select LTRIM(' barbar');
- > 'barbar'
RTRIM(str)
Returns the string str with its trailing space character removed.
mysql > select RTRIM('barbar ');
- > 'barbar'
This function is reliable for multiple bytes.
TRIM([[BOTH | LEA
DING | TRAILING] [remstr] FROM] str)
Returns the string str with all remstr prefixes or suffixes removed. If no modifiers BOTH, LEADING, or TRAILING are given, BOTH is assumed. If remstr is not specified, the space is 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 reliable for multiple bytes.
SOUNDEX(str)
Returns one homophone string of str. Two strings that sound "roughly the same" should have the same homophonic string. A "standard" homophone string is four characters long, but the SOUNDEX() function returns a string of any length. You can use SUBSTRING() on the result to get a "standard" homonym. All non-alphanumeric characters are ignored in the given string. All characters other than A-Z are treated as vowels.
mysql > select SOUNDEX('Hello');
- > 'H400'
mysql > select SOUNDEX('Quadratically');
- > 'Q36324'
SPACE(N)
Returns a single string consisting of N space characters.
mysql > select SPACE(6);
- > ' '
REPLACE(str,from_str,to_str)
Returns the string str, where all occurrences of the string from_str are replaced by the string to_str.
mysql > select REPLACE('www.mysql.com', 'w', 'Ww');
- > 'WwWwWw.mysql.com'
This function is reliable for multiple bytes.
REPEAT(str,count)
Returns a single string consisting of the string str that repeats countTimes times. If count < = 0, returns an empty string. If str or count are NULL, return NULL.
mysql > select REPEAT('MySQL', 3);
- > 'MySQLMySQLMySQL'
REVERSE(str)
Returns the string str in reverse character order.
mysql > select REVERSE('abc');
- > 'cba'
This function is reliable for multiple bytes.
INSERT(str,pos,len,newstr)
Returns the string str, the substring starting at the position pos and the len character long substring replaced by the string newstr.
mysql > select INSERT('Quadratic', 3, 4, 'What');
- > 'QuWhattic'
This function is reliable for multiple bytes.
ELT(N,str1,str2,str3,...)
If N= 1, return str1, if N= 2, return str2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the inverse of FIELD().
mysql > select ELT(1, 'ej', 'Heja', 'hej', 'foo');
- > 'ej'
mysql > select ELT(4, 'ej', 'Heja', 'hej', 'foo');
- > 'foo'
FIELD(str,str1,str2,str3,...)
Return str at str1, str2, str3... Index of the list. If str is not found, return 0. FIELD() is the inverse 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)
If the string str is in a table strlist consisting of N substrings, return 1 value from 1 to N. A string table is a string of substrings separated by ", ". If the first argument is a constant string and the second argument is a column of type SET, the FIND_IN_SET() function is optimized to use bit operations! Returns 0 if str is not in strlist or if strlist is an empty string. If any 1 parameter is NULL, return NULL. If the first argument contains 1 ", "the function will not work properly.
mysql > SELECT FIND_IN_SET('b','a,b,c,d');
- > 2
MAKE_SET(bits,str1,str2,...)
Returns a collection (containing a string of substrings separated by the ", "character) consisting of the string of the corresponding bit in the bits collection. str1 corresponds to bit 0, str2 corresponds to bit 1, and so on. In str1, str2,... The NULL string in is not added 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(0,'a','b','c');
- > ''
EXPORT_SET(bits,on,off,[separator,[number_of_bits]])
Returns 1 string, where you get 1 "on" string for each bit set in "bits" and 1 "off" string for each bit reset (reset). Each string is separated by "separator" (default ", "), and only "number_of_bits" (default 64) bits of "bits" are used.
mysql > select EXPORT_SET(5,'Y','N',',',4)
- > Y,N,Y,N
LCASE(str)
LOWER(str)
Returns the string str and changes all characters to lowercase according to the current character set map (the default is ISO-8859-1 Latin1). This function is reliable for multiple bytes.
mysql > select LCASE('QUADRATICALLY');
- > 'quadratically'
UCASE(str)
UPPER(str)
Returns the string str, changing all characters to uppercase according to the current character set map (the default is ISO-8859-1 Latin1). This function is reliable for multiple bytes.
mysql > select UCASE('Hej');
- > 'HEJ'
This function is reliable for multiple bytes.
LOAD_FILE(file_name)
Reads in the file and returns the file contents as a string. The file must be on the server, you must specify the full path name to the file, and you must have file permissions. Everything in the file must be readable and smaller than max_allowed_packet. The function returns NULL if the file does not exist or cannot be read for one of the above reasons.
mysql > UPDATE table_name
SET blob_column=LOAD_FILE("/tmp/picture")
WHERE id=1;
MySQL automatically converts Numbers to strings if necessary, and vice versa:
mysql > SELECT 1+"1";
- > 2
mysql > SELECT CONCAT(2,' test');
- > '2 test'
If you want to explicitly transform 1 number to 1 string, pass it as an argument to CONCAT().
If the string function provides a binary string as an argument, the resulting string is also a binary string. A number that is converted to 1 string is treated as a 2-base string. This only affects comparison.
for operations against string positions, the first position is marked as 1.
ASCII(str)
Returns the ASCII code value for the leftmost character of the string str. If str is an empty string, return 0. If str is NULL, return NULL.
mysql > select ASCII('2');
- > 50
mysql > select ASCII(2);
- > 50
mysql > select ASCII('dx');
- > 100
Also see the ORD() function.
ORD(str)
If the left-most character of the string str is 1 multi-byte character, pass the format ((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...) Returns the ASCII code value of the character to return the multi-byte character code. If the leftmost character is not a multi-byte character. Returns the same value as the ASCII() function.
mysql > select ORD('2');
- > 50
CONV(N,from_base,to_base)
You switch Numbers between bases. Returns the string number of the number N, transformed from from_base to to_base basis, and NULL if any parameter is NULL. The parameter N is interpreted as 1 integer, but can be specified as 1 integer or 1 string. The smallest base is 2 and the largest base is 36. If to_base is a negative number, N is considered a signed number; otherwise, N is considered an unsigned number. CONV works with 64 point accuracy.
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'
BIN(N)
Returns a string representation of the 2-base value N, where N is a long integer (BIGINT) number, which is equivalent to CONV(N,10,2). If N is NULL, return NULL.
mysql > select BIN(12);
- > '1100'
OCT(N)
Returns a string representation of the 8-base value N, where N is a long integer number, which is equivalent to CONV(N,10,8). If N is NULL, return NULL.
mysql > select OCT(12);
- > '14'
HEX(N)
Returns a representation of the base 106 value N1 string, where N is a long integer (BIGINT) number, which is equivalent to CONV(N,10,16). If N is NULL, return NULL.
mysql > select HEX(255);
- > 'FF'
CHAR(N,...)
CHAR() interprets arguments as integers and returns a string made up of ASCII code characters for these integers. The NULL value is skipped.
mysql > select CHAR(77,121,83,81,'76');
- > 'MySQL'
mysql > select CHAR(77,77.3,'77.3');
- > 'MMM'
CONCAT(str1,str2,...)
Returns a string from the parameter link. If any parameter is NULL, return NULL. You can have more than two arguments. One numeric parameter is converted to an equivalent string.
mysql > select CONCAT('My', 'S', 'QL');
- > 'MySQL'
mysql > select CONCAT('My', NULL, 'QL');
- > NULL
mysql > select CONCAT(14.3);
- > '14.3'
LENGTH(str)
OCTET_LENGTH(str)
CHAR_LENGTH(str)
CHARACTER_LENGTH(str)
Returns the length of the string str.
mysql > select LENGTH('text');
- > 4
mysql > select OCTET_LENGTH('text');
- > 4
Note that for multi-byte characters, its CHAR_LENGTH() is evaluated only once.
LOCATE(substr,str)
POSITION(substr IN str)
Returns the position where the substring substr appears in the first string str, and if substr is not in str, returns 0.
mysql > select LOCATE('bar', 'foobarbar');
- > 4
mysql > select LOCATE('xbar', 'foobar');
- > 0
This function is multi-byte reliable.
LOCATE(substr,str,pos)
Returns the position of the substring substr at the first occurrence of the string str, starting at pos. If substr is not in str, return 0.
mysql > select LOCATE('bar', 'foobarbar',5);
- > 7
This function is multi-byte reliable.
INSTR(str,substr)
Returns the position where the substring substr occurs at the first of the string str. This is the same as LOCATE() with two arguments, except that the arguments are reversed.
mysql > select INSTR('foobarbar', 'bar');
- > 4
mysql > select INSTR('xbar', 'foobar');
- > 0
This function is multi-byte reliable.
LPAD(str,len,padstr)
Returns the string str, filling the left side with the string padstr until str is len characters long.
mysql > select LPAD('hi',4,'??');
- > '??hi'
RPAD(str,len,padstr)
Returns the string str, filling the right side with the string padstr until str is len characters long.
mysql > select RPAD('hi',5,'?');
- > 'hi???'
LEFT(str,len)
Returns the leftmost len character of the string str.
mysql > select LEFT('foobarbar', 5);
- > 'fooba'
This function is multi-byte reliable.
RIGHT(str,len)
Returns the rightmost len character of the string str.
mysql > select RIGHT('foobarbar', 4);
- > 'rbar'
This function is multi-byte reliable.
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)
MID(str,pos,len)
Returns a substring of len characters from the string str, starting at the position pos. The variant form using FROM is the ANSI SQL92 syntax.
mysql > select SUBSTRING('Quadratically',5,6);
- > 'ratica'
This function is multi-byte reliable.
SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
Returns a substring from pos at the beginning of the string str.
mysql > select SUBSTRING('Quadratically',5);
- > 'ratically'
mysql > select SUBSTRING('foobarbar' FROM 4);
- > 'barbar'
This function is multi-byte reliable.
SUBSTRING_INDEX(str,delim,count)
Returns the substring after delim from the count separator of the string str. If count is positive, returns the last separator to all characters to the left (counting from the left). If count is negative, return the last separator to all characters on the right (counting from the right).
mysql > select SUBSTRING_INDEX('www.mysql.com', '.', 2);
- > 'www.mysql'
mysql > select SUBSTRING_INDEX('www.mysql.com', '.', -2);
- > 'mysql.com'
This function is reliable for multiple bytes.
LTRIM(str)
Returns the string str with its leading space character removed.
mysql > select LTRIM(' barbar');
- > 'barbar'
RTRIM(str)
Returns the string str with its trailing space character removed.
mysql > select RTRIM('barbar ');
- > 'barbar'
This function is reliable for multiple bytes.
TRIM([[BOTH | LEA
DING | TRAILING] [remstr] FROM] str)
Returns the string str with all remstr prefixes or suffixes removed. If no modifiers BOTH, LEADING, or TRAILING are given, BOTH is assumed. If remstr is not specified, the space is 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 reliable for multiple bytes.
SOUNDEX(str)
Returns one homophone string of str. Two strings that sound "roughly the same" should have the same homophonic string. A "standard" homophone string is four characters long, but the SOUNDEX() function returns a string of any length. You can use SUBSTRING() on the result to get a "standard" homonym. All non-alphanumeric characters are ignored in the given string. All characters other than A-Z are treated as vowels.
mysql > select SOUNDEX('Hello');
- > 'H400'
mysql > select SOUNDEX('Quadratically');
- > 'Q36324'
SPACE(N)
Returns a single string consisting of N space characters.
mysql > select SPACE(6);
- > ' '
REPLACE(str,from_str,to_str)
Returns the string str, where all occurrences of the string from_str are replaced by the string to_str.
mysql > select REPLACE('www.mysql.com', 'w', 'Ww');
- > 'WwWwWw.mysql.com'
This function is reliable for multiple bytes.
REPEAT(str,count)
Returns a single string consisting of the string str that repeats countTimes times. If count < = 0, returns an empty string. If str or count are NULL, return NULL.
mysql > select REPEAT('MySQL', 3);
- > 'MySQLMySQLMySQL'
REVERSE(str)
Returns the string str in reverse character order.
mysql > select REVERSE('abc');
- > 'cba'
This function is reliable for multiple bytes.
INSERT(str,pos,len,newstr)
Returns the string str, the substring starting at the position pos and the len character long substring replaced by the string newstr.
mysql > select INSERT('Quadratic', 3, 4, 'What');
- > 'QuWhattic'
This function is reliable for multiple bytes.
ELT(N,str1,str2,str3,...)
If N= 1, return str1, if N= 2, return str2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the inverse of FIELD().
mysql > select ELT(1, 'ej', 'Heja', 'hej', 'foo');
- > 'ej'
mysql > select ELT(4, 'ej', 'Heja', 'hej', 'foo');
- > 'foo'
FIELD(str,str1,str2,str3,...)
Return str at str1, str2, str3... Index of the list. If str is not found, return 0. FIELD() is the inverse 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)
If the string str is in a table strlist consisting of N substrings, return 1 value from 1 to N. A string table is a string of substrings separated by ", ". If the first argument is a constant string and the second argument is a column of type SET, the FIND_IN_SET() function is optimized to use bit operations! Returns 0 if str is not in strlist or if strlist is an empty string. If any 1 parameter is NULL, return NULL. If the first argument contains 1 ", "the function will not work properly.
mysql > SELECT FIND_IN_SET('b','a,b,c,d');
- > 2
MAKE_SET(bits,str1,str2,...)
Returns a collection (containing a string of substrings separated by the ", "character) consisting of the string of the corresponding bit in the bits collection. str1 corresponds to bit 0, str2 corresponds to bit 1, and so on. In str1, str2,... The NULL string in is not added 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(0,'a','b','c');
- > ''
EXPORT_SET(bits,on,off,[separator,[number_of_bits]])
Returns 1 string, where you get 1 "on" string for each bit set in "bits" and 1 "off" string for each bit reset (reset). Each string is separated by "separator" (default ", "), and only "number_of_bits" (default 64) bits of "bits" are used.
mysql > select EXPORT_SET(5,'Y','N',',',4)
- > Y,N,Y,N
LCASE(str)
LOWER(str)
Returns the string str and changes all characters to lowercase according to the current character set map (the default is ISO-8859-1 Latin1). This function is reliable for multiple bytes.
mysql > select LCASE('QUADRATICALLY');
- > 'quadratically'
UCASE(str)
UPPER(str)
Returns the string str, changing all characters to uppercase according to the current character set map (the default is ISO-8859-1 Latin1). This function is reliable for multiple bytes.
mysql > select UCASE('Hej');
- > 'HEJ'
This function is reliable for multiple bytes.
LOAD_FILE(file_name)
Reads in the file and returns the file contents as a string. The file must be on the server, you must specify the full path name to the file, and you must have file permissions. Everything in the file must be readable and smaller than max_allowed_packet. The function returns NULL if the file does not exist or cannot be read for one of the above reasons.
mysql > UPDATE table_name
SET blob_column=LOAD_FILE("/tmp/picture")
WHERE id=1;
MySQL automatically converts Numbers to strings if necessary, and vice versa:
mysql > SELECT 1+"1";
- > 2
mysql > SELECT CONCAT(2,' test');
- > '2 test'
If you want to explicitly transform 1 number to 1 string, pass it as an argument to CONCAT().
If the string function provides a binary string as an argument, the resulting string is also a binary string. A number that is converted to 1 string is treated as a 2-base string. This only affects comparison.