MySQL string function

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

ASCII(str)

Returns the str ASCII code value of the leftmost character of the string. If str It is an empty string, return 0 . If str Yes NULL , return NULL .

Mysql> Select ASCII (' 2 ');
        50
Mysql> Select ASCII (2);
        50
Mysql> Select ASCII (' DX ');
        100

You can also see the Ord () function.

ORD(str)

If the leftmost character of the string str is a multibyte character, the ((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...] multibyte character code is returned by the ASCII code value that returns the character in the format. If the leftmost character is not a multi-byte character. Returns ASCII() the same value returned from the function.

Mysql> Select ORD (' 2 ');
        50

CONV(N,from_base,to_base)

The numbers are transformed between different digital bases. Returns a numeric N string number, from the from_base base transform to the to_base base, if any argument is NULL , is returned NULL . Parameters are N interpreted as an integer, but can be specified as an integer or as a string. The minimum base is 2 and the largest base is 36 . If to_base it is a negative number, it is considered to be N a signed count, otherwise, it is N treated as an unsigned number. CONVwork 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 (+ + ' + ' +0xa,10,10);
        ' 40 '

BIN(N)

returns N A string representation of a binary value, in this case N a long Integer ( BIGINT ) number, which is equivalent to CONV(N,10,2) . If N Yes NULL , return NULL .

Mysql> Select BIN (12);
        ' 1100 '

OCT(N)

returns the N representation of a string of octal values, in this case N a long integer number, which is equivalent to CONV(N,10,8) . If N it is null, return NULL .

Mysql> Select OCT (12);
        ' 14 '

HEX(N)

returns the hexadecimal value N of a string representation, in this case N a long Integer ( BIGINT ) number, which is equivalent to CONV(N,10,16) . If N Yes NULL , return NULL .

Mysql> Select HEX (255);
        ' FF '

CHAR(N,...)

CHAR() interprets parameters as integers and returns a string consisting of ASCII code characters for those 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 the string from the parameter link . If any of the parameters are NULL , return NULL . There can be more than 2 parameters. A numeric parameter is transformed into an equivalent string form.

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 str the length of the string.

Mysql> Select LENGTH (' text ');
        4
Mysql> Select Octet_length (' text ');
        4

Note that for multibyte characters, its char_length () is evaluated only once.

LOCATE(substr,str)

　

POSITION(substr IN str)

returns the substring substr at the str first occurrence of the string, if substr not str inside, to return 0 .

Mysql> Select LOCATE (' Bar ', ' Foobarbar ');
        4
Mysql> Select LOCATE (' Xbar ', ' foobar ');
        0

The function is multi-byte reliable.  

LOCATE(substr,str,pos)

Returns the substring substr at the str first occurrence of the string, starting at the position pos . If substr not str inside, return 0 .

Mysql> Select LOCATE (' Bar ', ' Foobarbar ', 5);
        7

This function is multi-byte reliable.

INSTR(str,substr)

returns the position of the substr first occurrence of a substring in a string str . This is the same as having 2 parameter forms LOCATE() , 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 str A string that is padded to the left with padstr a string until str it is len a character long.

Mysql> Select Lpad (' Hi ', 4, '?? ');
        '?? Hi

RPAD(str,len,padstr)

Returns str A string that fills the right side with padstr a string until str it is len a character long.

Mysql> Select Rpad (' Hi ', 5, '? ');
        ' Hi??? '

LEFT(str,len)

returns the str leftmost character of a string len .

Mysql> Select Left (' Foobarbar ', 5);
        ' Fooba '

The function is multi-byte reliable.

RIGHT(str,len)

returns the rightmost str character of len a string 。

Mysql> Select Right (' Foobarbar ', 4);
        ' Rbar '

The function is multi-byte reliable.

SUBSTRING(str,pos,len)

　

SUBSTRING(str FROM pos FOR len)

　

MID(str,pos,len)

strreturns a len substring of characters from a string, starting at the position pos . FROMthe variant form used is the ANSI SQL92 syntax.

Mysql> Select SUBSTRING (' quadratically ', 5,6);
        ' Ratica '

The function is multi-byte reliable.

SUBSTRING(str,pos)

　

SUBSTRING(str FROM pos)

str returns a substring from the starting position of the string pos .

Mysql> Select SUBSTRING (' quadratically ', 5);
        ' Ratically '
Mysql> Select SUBSTRING (' Foobarbar ' from 4);
        ' Barbar '

The function is multi-byte reliable.

SUBSTRING_INDEX(str,delim,count)

Returns a str count 的 substring that follows the delimiter from the first occurrence delim of a string. If count it is a positive number, returns all characters from the last delimiter to the left (from the left). If count it is negative, returns the last delimiter to the right of all characters (from the right number).

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 a string that has its preceding space character removed str .

Mysql> Select LTRIM ('  barbar ');
        ' Barbar '

RTRIM(str)

returns a string that has its trailing space character removed str .

Mysql> Select RTRIM (' Barbar   ');
        ' Barbar '

This function is reliable for multiple bytes.  

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

Returns str a string with all its remstr prefixes or suffixes removed. If there is no modifier BOTH , LEADING or TRAILING given, BOTH is assumed. If remstr not specified, the space is 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)

returns str an homophonic string. The 2 strings that sound "roughly the same" should have the same homophonic string. A "standard" string length is 4 characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use the result to SUBSTRING() get a "standard" string of sounds. All non-numeric alphabetic characters are ignored in the given string. All characters outside A-Z are used as vowels.

Mysql> Select SOUNDEX (' Hello ');
        ' H400 '
Mysql> Select SOUNDEX (' quadratically ');
        ' Q36324 '

SPACE(N)

returns N a string consisting of a space character.

Mysql> Select SPACE (6);
        "      "

REPLACE(str,from_str,to_str)

returns str a string in which from_str all occurrences of a string are substituted by a string to_str .

Mysql> Select REPLACE (' www.mysql.com ', ' w ', ' Ww ');
        ' WwWwWw.mysql.com '

This function is reliable for multiple bytes.

REPEAT(str,count)

returned by duplicate count strA string consisting of a string of times. If count <= 0 , returns an empty string. If str or count Yes NULL , return NULL .

Mysql> Select REPEAT (' MySQL ', 3);
        ' Mysqlmysqlmysql '

REVERSE(str)

Returns a string that reverses the order of characters str .

Mysql> Select REVERSE (' abc ');
        ' CBA '

This function is reliable for multiple bytes.

INSERT(str,pos,len,newstr)

returns str a string in which the substring at pos the beginning of the position and len characters long are replaced by strings newstr .

Mysql> Select INSERT (' Quadratic ', 3, 4, ' what ');
        ' Quwhattic '

This function is reliable for multiple bytes.

ELT(N,str1,str2,str3,...)

If N = 1 , returns, str1 if N = 2 , returns str2 , and so on. Nreturns if less than 1 or greater than the number of arguments NULL . ELT()is an 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,...)

returns the index in,,, str of the str1 str2 str3 ... manifest. If str not found, return 0 . FIELD()is an 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 str is in N a table made up of substrings strlist , returns a 1 value to it N . A string table is “,” a string that consists of delimited substrings. If the first argument is a constant string and the second argument is a SET column of type, the FIND_IN_SET() function is optimized and the bitwise operation is used! If str it is not strlist inside or if strlist it is an empty string, return 0 . If any one of the arguments is NULL , return NULL . If the first parameter contains one “,” , the function will not work correctly.

mysql> SELECT find_in_set (' B ', ' a,b,c,d ');
        2

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

Returns a collection that contains “,” a string of substrings separated by characters, consisting of a string of corresponding bits in the bits collection. str1corresponds to bit 0, str2 corresponds to bit 1, and so on. In str1 , str2 the ... string in is NULL 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 where you get an "on" string for each bit in "bits", and for each reset (reset) bit, you get a "off" string. Each string is delimited with "separator" (Default ","), and only "bits" of "number_of_bits" (default 64) bits are used.

Mysql> Select Export_set (5, ' Y ', ' N ', ', ', 4)
        

LCASE(str)

　

LOWER(str)

returns a string str that changes all characters to lowercase based on the current character set mapping (the default is Iso-8859-1 Latin1). This function is reliable for multiple bytes.

Mysql> Select LCASE (' quadratically ');
        ' Quadratically '

UCASE(str)

　

UPPER(str)

returns a string str that changes all characters to uppercase based on the current character set mapping (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 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. The file must have all the contents readable and less than max_allowed_packet . If the file does not exist or because one of the above reasons cannot be read, the function returns NULL .

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

MySQL automatically transforms numbers into strings when necessary, and vice versa:

Mysql> Select 1+ "1";
        2
Mysql> SELECT CONCAT (2, ' test ');
        ' 2 test '

If you want to explicitly transform a number into a string, pass it as an argument CONCAT() .

If a string function provides a binary string as a parameter, the resulting string is also a binary string. A number that is transformed into a string is treated as a binary string.

REF

Http://www.cnblogs.com/showker/archive/2010/03/15/1685874.html