MySQL string functions

For operations on the string position, the first position is marked as 1.

ASCII(str)

Returns a string.strASCII code value of the leftmost character.If strIs a null string and returns 0. If strYes NULL, Return NULL.

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

For more information, see the ord () function.

ORD(str)

If the leftmost character of a string 'str' is a multi-byte character ((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]Return the ASCII code value of the character to return the multi-byte code. If the leftmost character is not a multi-byte character. Returns and ASCII()The same value returned by the number of functions.

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

CONV(N,from_base,to_base)

Convert numbers between different digit bases. Return number NString number, from from_baseBase transform to_baseBase, if any parameter is NULL, Return NULL. Parameters NIt is interpreted as an integer, but can be specified as an integer or a string. Minimum base is 2And the largest base is 36. If to_baseIs a negative number, NIt is considered as a signed number. Otherwise, NIt is treated as an unsigned number. CONVWork with 64-point 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'
 

BIN(N)

Returns the binary value. NIn NIs a long integer ( BIGINT) Number, which is equivalent CONV(N,10,2). If NYes NULL, Return NULL.

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

OCT(N)

Returns the octal value. NRepresents a string in NIs a long integer, which is equivalent CONV(N,10,8). If NIs null, return NULL.

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

HEX(N)

Returns the hexadecimal value. NRepresents a string. NIs a long integer ( BIGINT) Number, which is equivalent CONV(N,10,16). If NYes NULL, Return NULL.

mysql> select HEX(255);
        -> 'FF'
 

CHAR(N,...)

CHAR()Interpreted as an integer and returns a string consisting of ASCII code characters of these integers. NULLThe 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. There can be more than two parameters. A 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 a string. str.

mysql> select LENGTH('text');
        -> 4
mysql> select OCTET_LENGTH('text');
        -> 4

Note that for multi-byte characters, its char_length () is calculated only once.

LOCATE(substr,str)

　

POSITION(substr IN str)

Returns a substring. substrIn the string strThe first position that appears, if substrNot in strAnd 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 a substring. substrIn the string strThe first position that appears, starting from the position posStart. If substrNot in strInside, return 0.

mysql> select LOCATE('bar', 'foobarbar',5);
        -> 7

This function is multi-byte reliable.

INSTR(str,substr)

Returns a substring. substrIn the string strThe first position in. This is in the form of two parameters LOCATE()In the same way, except that the parameters 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 a string. str, Use a string on the left padstrFill strYes lenCharacters long.

mysql> select LPAD('hi',4,'??');
        -> '??hi'
 

RPAD(str,len,padstr)

Returns a string. str, String on the right padstrFill strYes lenCharacters long.

mysql> select RPAD('hi',5,'?');
        -> 'hi???'

LEFT(str,len)

Returns a string. strThe leftmost area lenCharacters.

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

This function is multi-byte reliable.

RIGHT(str,len)

Returns a string. strThe rightmost lenCharacters 。

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)

From string strReturns lenSubstring, starting from position posStart. Use FROMThe variant form 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)

From string strStart position posReturns a substring.

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 string strThe countAppears OfDelimiter delimSubstring. If countIs a positive number, returns all characters from the last separator to the left (number from the left. If countIs a negative number, returns all characters (from the right) from the last separator to 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 with leading space characters deleted. str.

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

RTRIM(str)

Returns the string with spaces after the string is deleted. str.

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

This function is reliable for multiple bytes.

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

Returns a string. str, All of them remstrThe prefix or suffix is deleted. If there is no Modifier BOTH, LEADINGOr TRAILINGDeliver, BOTHIs assumed. If remstrNot specified. spaces are deleted.

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)

Return str. The two strings that sound "roughly the same" should have the same homophone string. The length of a "standard" homophone string is 4 characters, SOUNDEX()Returns a string of any length. You can use SUBSTRING()Get a standard homophone. All non-alphanumeric characters are ignored in a given string. All international letters except the A-Z are treated as vowels.

mysql> select SOUNDEX('Hello');
        -> 'H400'
mysql> select SOUNDEX('Quadratically');
        -> 'Q36324'
 

SPACE(N)

Returns NA string consisting of space characters.

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

REPLACE(str,from_str,to_str)

Returns a string. str, Its string from_strAll appear by string to_strReplacement.

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

This function is reliable for multiple bytes.

REPEAT(str,count)

Returns a duplicate countTimes string strA string. If count <= 0Returns an empty string. If strOr countYes NULL, Return NULL.

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

REVERSE(str)

Returns a string in the reversed character order. str.

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

This function is reliable for multiple bytes.

INSERT(str,pos,len,newstr)

Returns a string. str, In the location posAnd lenCharacter long substring by 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, Such as fruit N= 2, Return str2And so on. If NLess 1Or greater than the number of parameters, return NULL. ELT()Yes FIELD()Inverse Operation.

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

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

Return strIn str1, str2, str3, ...Clear the index of a ticket. If strNo. 0. FIELD()Yes ELT()Inverse Operation.

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 strIn NTable composed of substrings strlistIn, return 1To N. A string table is ","A string composed of substrings. If the first parameter is a constant string and the second parameter is of SET, FIND_IN_SET()Bitwise operations are used when functions are optimized! If strNot in strlistInside or if strlistIs a null string and returns 0. If any parameter is NULL, Return NULL. If the first parameter contains ","This function will not work properly.

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

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

Returns a set (including ","A string consisting of substrings separated by characters. bitsThe string in the set. str1Corresponding to bit 0, str21, and so on. In str1, str2, ...In NULLThe string 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 a string. Here, for each bit set in "bits", you get a "on" string, and for each reset (reset) bit, you get a "off" string. Each string is separated by "separator" (default ","), and only the "number_of_bits" (default 64) bits are used.

mysql> select EXPORT_SET(5,'Y','N',',',4)
        -> Y,N,Y,N 

LCASE(str)

　

LOWER(str)

Returns a string. strAccording to the current character set ing (iso-8859-1 Latin1 by default), all characters are changed to lower case. This function is reliable for multiple bytes.

mysql> select LCASE('QUADRATICALLY');
        -> 'quadratically'
 

UCASE(str)

　

UPPER(str)

Returns a string. strAccording to the current character set ing (iso-8859-1 Latin1 by default), all characters are changed to uppercase. This function is reliable for multiple bytes.

mysql> select UCASE('Hej');
        -> 'HEJ'

This function is reliable for multiple bytes.

LOAD_FILE(file_name)

Read the file and return the file content as a string. The file must be on the server, you must specify the full path name of the file, and you must have FilePermission. All file content must be readable and smaller max_allowed_packet. If the file does not exist or cannot be read due to one of the above reasons, the function returns NULL.

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

MySQLIf necessary, the number is automatically converted into a string, and the reverse is also true:

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

If you want to explicitly convert a number to a string, pass it as a parameterCONCAT().

If a string function provides a binary string as a parameter, the result string is also a binary string. The number converted to a string is treated as a binary string. This only has a higher impact ratio.