MySQL String function Collection comparison full _mysql

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 using the ASCII code value of the character returned in the format. If the leftmost character is not a multibyte character. Returns ASCII() the same value returned by the function.

Mysql> Select ORD (' 2 ');
    -> 50

CONV(N,from_base,to_base)

The

transforms a number between different numeric bases. Returns the string number of the number n , from the from_base Base transformation to to_base Base, if any parameter is null , returns null . The parameter n is interpreted as an integer, but can be specified as an integer or a string. The minimum base is the 2 and the largest base is 36 . If the to_base is a negative number, n is considered to be a signed Otherwise, the n is treated as an unsigned number. The 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+ "+" +0xa,10,10); 
 -> ' A ' 
 

BIN(N)

returns a string representation of a binary value, which N N is 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 representation of a string representing the octal value, which N N is 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 N representation of a string of hexadecimal values, here N is 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 of ASCII code characters for these integers. 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 argument's link . If any argument is NULL , return NULL . can have 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 substr position of the substring in the str first occurrence of the string, if substr not str inside 0 .

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

The function is multiple-byte reliable.  

LOCATE(substr,str,pos)

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

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

This function is multibyte-reliable.

INSTR(str,substr)

returns the substr str position of the first occurrence of a substring in a string. 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 multibyte-reliable.

LPAD(str,len,padstr)

returns str a string, and the left padstr is filled with 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 padstr is filled with a string until str it is len a character long.

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

LEFT(str,len)

returns str the leftmost character of len a string.

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

The function is multiple-byte reliable.

RIGHT(str,len)

Returns str the rightmost character of len a string 。

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

The function is multiple-byte reliable.

SUBSTRING(str,pos,len)

　

SUBSTRING(str FROM pos FOR len)

　

MID(str,pos,len)

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

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

The function is multiple-byte reliable.

SUBSTRING(str,pos)

　

SUBSTRING(str FROM pos)

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

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

The function is multiple-byte reliable.

SUBSTRING_INDEX(str,delim,count)

returns a substring after the first occurrence of a delimiter in a string str count 的 delim . If this count is a positive number, return the last separator to all characters to the left (from the left). If count it is a negative number, returns the last separator to all characters on the right (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 a string that deletes its preceding space character str .

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

RTRIM(str)

returns the string whose trailing space character has been deleted 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 of its remstr prefixes or suffixes removed. If no modifier BOTH , LEADING or TRAILING given, BOTH is assumed. If remstr it is 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 a homonym string. The 2 strings that sound "roughly the same" should have the same homonym string. A "standard" homonym string length is 4 characters, but the SOUNDEX() function returns an arbitrarily long string. You can use the results to SUBSTRING() get a "standard" of the homophonic string. All non-numeric alphabetic characters are ignored in the given string. All characters outside A-Z are treated 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 the string are replaced by strings to_str .

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

This function is reliable for multiple bytes.

REPEAT(str,count)

returns the 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 a string in which the substring str at the pos beginning of the position and the substring of len characters 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 , return str1 , if N = 2 , return str2 , and so on. Nreturns if 1 the number of arguments is less than or greater than NULL . ELT()is a FIELD() reverse 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 str the str1 index of,,, the str2 str3 ... list. If str not found, return 0 . FIELD()is a ELT() reverse 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 in a table made up of n Child Strings Font face= "Nsimsun" >strlist , returns a 1 to ". If the first argument is a constant string and the second argument is a column of type set , find_in_ The SET () function is optimized to use bit arithmetic! If the str is not in strlist inside or if The font face= "Nsimsun" >strlist is an empty string that returns 0 . If any one of the parameters is null , return null . If the first argument contains a , 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 containing the “,”字符分隔的子串组成的一个 字符串)，由相应的位在 bits集合中的的字符串组成。 str1对应于位0， str2对 应位1，等等。在 str1, str2, ...中 的 NULL串不添加到结果中。

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 you get a "off" string for each reset (reset) bit. Each string is delimited by "separator" (Default ",") and Only "Number_of_bits" (default 64) bits of "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 according to the current character set mapping (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 according to the current character set mapping (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 contents of the file as a string. Files must be on the server, you must specify the full pathname to the file, and you must have file permissions. The file must all content be readable and less than max_allowed_packet . The function returns if the file does not exist or because one of the above reasons cannot be read out 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 a parameter to 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. This affects only the comparison.