Mysql's String function usage instructions _mysql

A string is usually used as an action object, such as finding a substring in a string, seeking a substring, inserting a substring at a point in a string, and deleting a substring. The necessary and sufficient condition for two strings to be equal is that the length is equal and the characters on each corresponding position are equal. To set P and Q to be two strings, the operation of Q for the first place in P is called pattern matching. The two most basic types of storage for a string are sequential storage and linked storage.
let's look at the string functions in MySQL
If the length of the result is greater than the maximum value of the Max_allowed_packet system variable, the return value of the string value function is null.
For functions that operate at the string position, the first position is numbered 1.
ASCII (str)
Returns the value of the leftmost character of String Str. If Str is an empty string, the return value is 0. If STR is NULL, the return value is null. ASCII () is used for characters with numeric values ranging from 0 to 255.
mysql> SELECT ASCII (' 2′);
-> 50
Mysql> SELECT ASCII (2);
-> 50
mysql> SELECT ASCII (' DX ');
-> 100
See ORD () function.
BIN (N)
Returns a string representation of the binary value of n, where N is a longlong (BIGINT) number. This equates to CONV (n,10,2). If n is null, the return value is null.
Mysql> SELECT BIN (a);-> ' 1100′
Bit_length (str)
Returns the string str length of the binary value.
mysql> SELECT bit_length (' text ');-> 32
CHAR (N,... [USING CharSet])
CHAR () understands each parameter n as an integer that returns a string containing the character given by the code value of these integers. Null value is omitted.
Mysql> Select char (77,121,83,81, ' 76′ ');-> ' MySQL ' mysql> SELECT char (77,77.3, ' 77.3′ ');-> ' MMM '
The char () parameter greater than 255 is converted to a multiple result character. For example, char (256) is equivalent to char (1,0), while char (256*256) is equivalent to char (1,0,0):
Mysql> SELECT HEX (char (1,0)), HEX (char (256)), + —————-+ —————-+| HEX (CHAR (1,0)) | HEX (CHAR (256)) |+ —————-+ —————-+| 0100 | 0100 |+ —————-+ —————-+mysql> SELECT HEX (char (1,0,0)), HEX (char (256*256)); + —————— + —————— –+| HEX (CHAR (1,0,0)) | HEX (CHAR (256*256)) |+ —————— + —————— –+| 010000 | 010000 |+ —————— + —————— –+
The return value of CHAR () is a binary string. You can optionally use the using statement to produce a string in the given character set:
Mysql> Select CHARSET (char (0X65)), CHARSET (char (0x65 USING UTF8));mysql> select CHARSET (char (0X65)), CHARSET ( CHAR (0x65 USING UTF8); + ——————— + —————————— –+| CHARSET (CHAR (0x65)) | CHARSET (CHAR (0x65 USING UTF8)) |+ ——————— + —————————— –+| binary | UTF8 |+ ——————— + —————————— –+
If the using is already generated and the resulting string does not conform to the given character set, a warning is issued. Similarly, if the strict SQL schema is activated, the result of char () becomes NULL.
Char_length (str)
The return value is the length of the string str, and the length of the unit is a character. A multibyte character counts as a single word. For one containing five two-byte character sets, the LENGTH () return value is 10, and the return value of Char_length () is 5.
Character_length (str)
Character_length () is a synonym for Char_length ().
COMPRESS (string_to_compress)
Compresses a string. This function requires MySQL to have been compressed with a compressed library such as zlib. Otherwise, the return value is always null. Uncompress () can decompress compressed strings.
Mysql> Select Length (REPEAT (' a ', 1000));-> 21mysql> Select Length (COMPRESS ("));-> 0mysql> Select Length (COMPRESS (' a '));-> 13mysql> Select Length (COMPRESS (REPEAT (' a '));-> 15
The contents of the compressed string are stored in the following manner:
Empty strings are stored in an empty string.
The four-byte length of the uncompressed string of non-null strings is stored (first in low byte), followed by a compressed string. If the string ends with a space, a "." is added to the back. Number to prevent the occurrence of automatically removing the trailing space when the resulting value is stored in a char or varchar type of field column. (It is not recommended to use CHAR or varchar to store compressed strings.) It is best to use a BLOB column instead).
CONCAT (STR1,STR2,...)
Returns a string resulting from a connection parameter. If any of the arguments are null, the return value is null. There may be one or more parameters. If all parameters are non binary strings, the result is a non binary string. If the argument contains any twos feed string, the result is a binary string. A numeric parameter is converted to an equal binary string format; To avoid this, you can use explicit type cast, for example: SELECT CONCAT (CAST (Int_col as CHAR), Char_col)
Mysql> Select CONCAT (' i ', ' S ', ' QL ');-> ' MySQL ' mysql> SELECT CONCAT (' My ', NULL, ' QL ');-> nullmysql> T CONCAT (14.3);-> ' 14.3′
Concat_ws (SEPARATOR,STR1,STR2,...)
Concat_ws () represents CONCAT with Separator and is a special form of CONCAT (). The first parameter is the separator for the other arguments. The position of the separator is placed between the two strings to which you want to connect. The delimiter can be a string, or it can be another parameter. If the delimiter is null, the result is null. function ignores NULL values after any of the delimiter parameters.
mysql> SELECT concat_ws (', ', ', ', ' name ', ' Second name ', ' last Name ');-> ' the ' name,second name ' MySQL > SELECT concat_ws (', ', ', ', ', ', ', ', ', ', ', ', ') ';-> ' the ' name ' Name,last '
CONCAT_WS () does not ignore any empty strings. (All NULL is ignored, however).
CONV (N,from_base,to_base)
Converts numbers between different numbers of bases. The return value is the N-string representation of the number, which is converted from the From_base base to the To_base base. If any one of the arguments is NULL, the return value is null. The argument N is understood to be an integer, but can be specified as an integer or a string. The minimum cardinality is 2, and the maximum base is 36. If To_base is a negative number, then N is treated as a signed count. Otherwise, N is considered an unsigned number. The CONV () operates at a precision of 64 bits.
Mysql> Select CONV (' A ', 16,2);-> ' 1010′mysql> select CONV (' 6E ', 18,8);-> ' 172′mysql> Select CONV ( -17,10,- ;-> '-h ' mysql> SELECT CONV (10+ ' 10′+ ' 10′+0xa,10,10);-> ' 40′
ELT (N,STR1,STR2,STR3,...)
If n = 1, the return value is str1, and if n = 2, the return value is str2, and so on. If n is less than 1 or greater than the number of arguments, the return value is NULL. ELT () is the complement of FIELD ().
Mysql> Select ELT (1, ' ej ', ' Heja ', ' Hej ', ' foo ');-> ' ej ' mysql> SELECT ELT (4, ' ej ', ' Heja ', ' Hej ', ' the ', ', ');-> ' ' F Oo
Export_set (Bits,on,off[,separator[,number_of_bits]])
The return value is a string in which an on string is obtained for each bit group in the bits value, and a off string is obtained for each clear 0 bit bit. Bit values in bits are tested in Right-to-left order (from low bit to high bit). strings are separated by separate strings (the default is comma ', ') and are added to the results in Left-to-right order. Number_of_bits will give the number of bits tested (default 64).
Mysql> Select Export_set (5, ' Y ', ' N ', ', ', 4);-> ' y,n,y,n ' mysql> SELECT export_set (6, ' 1′, ' 0′, ', ', ');-> ' 0,1,1,0,0,0,0,0,0,0′
FIELD (STR,STR1,STR2,STR3,...)
The return value is STR1, str2, Str3,...... The STR index in the list. When STR is not found, the return value is 0.
If all parameters for field () are strings, all parameters are compared by string. If all the arguments are numbers, compare them by number. Otherwise, the parameters are compared in double.
If STR is NULL, the return value is 0, because null cannot be compared equally with any value. FIELD () is the complement of ELT ().
mysql> Select field (' EJ ', ' Hej ', ' ej ', ' Heja ', ' Hej ', ' foo ');-> 2mysql> select field (' Fo ', ' Hej ', ' ej ', ' Heja ', ') ' Hej ', ' foo ');-> 0
Find_in_set (Str,strlist)
If string str is in the strlist of a string list of n chains, the return value ranges from 1 to N. A list of strings is a string of strings that are separated by a number of ', ' symbols. If the first argument is a constant string, and the second is the Type SET column, the Find_in_set () function is optimized, using bit computations. If STR is not strlist or strlist is an empty string, the return value is 0. If any of the arguments are null, the return value is null. This function will not work correctly when the first argument contains a comma (', ').
mysql> SELECT find_in_set (' B ', ' a,b,c,d ');-> 2
FORMAT (X,D)
Set number x to format ' #,###,###.## ', which is rounded down to D-bit after the decimal point, and the result is a string.
HEX (n_or_s)
If n_or_s is a number, returns a string representation of the hexadecimal value n, where n is a longlong (BIGINT) number. This is equivalent to CONV (n,10,16).
If n_or_s is a string, the return value is a n_or_s hexadecimal string representation, where each character in each n_or_s is converted to two hexadecimal digits.
Mysql> Select HEX (255);-> ' FF ' mysql> select 0x616263;-> ' abc ' mysql> Select HEX (' abc ');-> 616263
INSERT (STR,POS,LEN,NEWSTR)
Returns the string str whose substring starts at the POS position and the Len character that has long been replaced by the string newstr. If the POS exceeds the string length, the return value is the original string. If Len is longer than the length of the other string, it is replaced from position pos. If any of the arguments are null, the return value is null.
Mysql> Select Insert (' Quadratic ', 3, 4, ' What ');-> ' quwhattic ' mysql> SELECT insert (' quadratic ',-1, 4, ' What ');- > ' quadratic ' mysql> SELECT INSERT (' Quadratic ', 3, ' What ');-> ' quwhat '
This function supports multibyte characters.
INSTR (STR,SUBSTR)
Returns the first occurrence of a string of str neutron strings. This is the same as locate (), unless the order of the parameters is reversed.
Mysql> Select INSTR (' Foobarbar ', ' Bar ');-> 4mysql> Select INSTR (' Xbar ', ' foobar ');-> 0
This function supports multibyte characters and is case-sensitive only if at least one argument is a binary string.
LCASE (str)
LCASE () is a synonym for LOWER ().
Left (Str,len)
Returns the leftmost character of Len starting from the string str.
Mysql> SELECT left (' Foobarbar ', 5);-> ' Fooba '
LENGTH (str)
The return value is the length of the string str, in bytes. A multibyte character counts as multiple bytes. This means that for a string containing 5 2-byte characters, the return value of LENGTH () is 10, and the return value of Char_length () is 5.
mysql> SELECT LENGTH (' text ');-> 4
Load_file (file_name)
Reads the file and returns the file in the form of a string. The location of the file must be on the server, you must make a full path name for the file, and you must also have the file concession. The file must be readable and the file capacity must be less than max_allowed_packet bytes.
If the file does not exist or cannot be read because it does not meet the above criteria, the function return value is NULL.
Mysql> UPDATE tbl_nameset blob_column=load_file ('/tmp/picture ') WHERE id=1;
LOCATE (SUBSTR,STR), LOCATE (Substr,str,pos)
The first syntax returns the first occurrence of the string substr of the str neutron string. The second syntax returns the first occurrence of the string str neutron string substr, starting at the Pos point. If SUBSTR is not in Str, the return value is 0.
Mysql> Select LOCATE (' Bar ', ' Foobarbar ');-> 4mysql> Select LOCATE (' Xbar ', ' foobar ');-> 0mysql> Select LOCATE (' Bar ', ' Foobarbar ', 5);-> 7
This function supports multibyte characters and is case-sensitive only if at least one argument is a binary string.
LOWER (str)
Returns the string str and all characters that have changed to lowercase from the latest Character Set mapping table (default is cp1252 Latin1).
mysql> SELECT LOWER (' quadratically ');-> ' quadratically '
This function supports multibyte characters.
Lpad (STR,LEN,PADSTR)
Returns the string str, whose left side is filled by the string padstr to the Len character length. If the length of STR is greater than Len, the return value is shortened to the Len character.
mysql> SELECT lpad (' Hi ', 4, '?? '); -> '?? Hi ' mysql> SELECT lpad (' Hi ', 1, '?? '); -> ' h '
LTRIM (str)
Returns the string str, whose boot space character is deleted.
mysql> SELECT LTRIM (' Barbar ');-> ' Barbar '
This function supports multibyte characters.
Make_set (BITS,STR1,STR2,...)
Returns a set value (a string containing a character string separated by ', '), consisting of a string that has a corresponding bit in the BITS group. STR1 corresponds to bit 0, str2 corresponds to bit 1, and so on. STR1, str2, ... The null value 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 (1 | 4, ' hello ', "nice", NULL, ' World ');-> ' hello ' mysql> SELECT make_set (0, ' a ', ' B ', ' C ');-> "
MID (Str,pos,len)
MID (Str,pos,len) is a synonym for SUBSTRING (str,pos,len).
OCT (N)
Returns a string representation of the octal value of N, where N is a longlong (BIGINT) number. This equates to conv (n,10,8). If n is null, the return value is null.
Mysql> SELECT OCT (a);-> ' 14′
Octet_length (str)
Octet_length () is a synonym for LENGTH ().
ORD (str)
If the leftmost character of String Str is a multibyte character, the code that returns the code is calculated by using the following formula to calculate its constituent byte value:
(1st byte code) + (2nd byte codex256) + (3rd byte codex2562) ...
If the leftmost character is not a multibyte character, then ORD () and function ASCII () return the same value.
mysql> SELECT ORD (' 2′);-> 50
POSITION (substr in str)
POSITION (substr in str) is a synonym for LOCATE (SUBSTR,STR).
QUOTE (str)
Cites a string that produces a result that can be used as a full escape data value in an SQL statement. The returned string is marked with single quotes, each with a single quotation mark (' "), a backslash (' \ '), an ASCII nul, and a control-z preceded by a backslash symbol. If the value of the argument is null, the word "NULL" without the single quotation mark is returned.
mysql> SELECT QUOTE (' don\ ' t! '); -> ' don\ ' t! ' mysql> SELECT QUOTE (null);-> null
REPEAT (Str,count)
Returns a string consisting of repeated string str, with the number of string str equal to count. If Count <= 0, an empty string is returned. If STR or count is NULL, NULL is returned.
mysql> SELECT REPEAT (' MySQL ', 3);-> ' Mysqlmysqlmysql '
REPLACE (STR,FROM_STR,TO_STR)
Returns the string str and all string from_str replaced by the string to_str.
mysql> SELECT REPLACE (' www.mysql.com ', ' w ', ' Ww ');-> ' WwWwWw.mysql.com '
This function supports multibyte characters.
REVERSE (str)
Returns the string str, in reverse order and character order.
mysql> SELECT REVERSE (' abc ');-> ' CBA '
This function supports multibyte characters.
Right (Str,len)
Starts with the string str and returns the most right Len character.
Mysql> SELECT Right (' Foobarbar ', 4);-> ' Rbar '
This function supports multibyte characters.
Rpad (STR,LEN,PADSTR)
Returns the string str, whose right side is filled by a string padstr to Len character length. If the length of the string str is greater than Len, the return value is shortened to the same length as the Len character.
mysql> SELECT rpad (' Hi ', 5, '? '); -> ' Hi??? ' mysql> SELECT rpad (' Hi ', 1, '? '); -> ' h '
This function supports multibyte characters.
RTRIM (str)
Returns the string str, and the trailing whitespace character is deleted.
mysql> SELECT RTRIM (' Barbar ');-> ' Barbar '
This function supports multibyte characters.
SOUNDEX (str)
Returns a soundex string from Str. Two strings with almost the same probe should have the same soundex string. A standard SOUNDEX string has a length of 4 characters, whereas the SOUNDEX () function returns a person with a string of length. You can use the substring () in the result to get a standard soundex string. In Str, all characters that are not in alphabetical order are ignored. All international alphabetic symbols that are not within a-Z range are considered vowels.
Mysql> Select SOUNDEX (' Hello ');-> ' h400′mysql> select SOUNDEX (' quadratically ');-> ' q36324′
Note: This function executes the original SOUNDEX algorithm, rather than the more popular enhanced version (such as D. described in Knuth). The difference is that the original version will first delete the vowel, followed by repetition, whereas the enhanced version first deletes the repetition and then deletes the vowel.
Expr1 SOUNDS like EXPR2
This is equivalent to SOUNDEX (EXPR1) = SOUNDEX (EXPR2).
Space (N)
Returns a string consisting of n-interval symbols.
Mysql> SELECT Space (6);-> '
SUBSTRING (Str,pos), SUBSTRING (str from POS) SUBSTRING (Str,pos,len), SUBSTRING (str from POS for Len)
The format without the Len parameter returns a substring from string str, starting at position pos. Format with len parameter returns a substring of the same length as Len character from String str, starting at position pos. Use the from format as the standard SQL syntax. It is also possible to use a negative value for the POS. If so, the position of the substring starts at the POS character at the end of the string, not at the beginning of the string. You can use a negative value for a POS in a function in the following format.
Mysql> Select SUBSTRING (' quadratically ', 5);-> ' ratically ' mysql> SELECT SUBSTRING (' Foobarbar ' from 4);-> ' Barbar ' mysql> Select SUBSTRING (' quadratically ', 5,6);-> ' Ratica ' mysql> SELECT SUBSTRING (' Sakila ',-3);-> ' Ila ' mysql> Select SUBSTRING (' Sakila ', -5, 3);-> ' Aki ' mysql> SELECT SUBSTRING (' Sakila ' FROM-4 for 2);-> ' Ki '
This function supports multibyte characters.
Note that if Len is using a value less than 1, the result is always an empty string.
SUBSTR () is a synonym for SUBSTRING ().
Substring_index (Str,delim,count)
Returns the string str from the string, before the delimiter Delim and Count appears. If Count is positive, it returns everything to the left of the final delimiter (starting from the left). If count is negative, all content on the right side of the delimiter (starting from the right) is returned.
Mysql> Select Substring_index (' www.mysql.com ', '. ', 2);-> ' Www.mysql ' mysql> SELECT substring_index (' Www.mysql.com ', '. ',-2);-> ' mysql.com '
This function supports multibyte characters.
TRIM ([{BOTH | Leading | Trailing} [REMSTR] from] str) TRIM (remstr from] str)
Returns the string str, where all remstr prefixes and/or suffixes have been deleted. If none of the classifier both, leadin, or trailing is given, it is assumed to be both. REMSTR is optional and can be deleted without specifying a space.
Mysql> Select Trim (' Bar ');-> ' bar ' mysql> SELECT trim (leading ' x ' from ' xxxbarxxx ');-> ' barxxx ' mysql> sele CT Trim (BOTH ' x ' xxxbarxxx ');-> ' bar ' mysql> SELECT TRIM (trailing ' xyz ' from ' barxxyz ');-> ' Barx '
This function supports multibyte characters.
UCASE (str)
UCASE () is a synonym for upper ().
Uncompress (string_to_uncompress)
The compressed string after the compress () function is decompressed. If the parameter is a compressed value, the result is NULL. This function requires MySQL to have been compiled by a compressed library such as zlib. Otherwise, the return value will always be NULL.
Mysql> Select Uncompress (COMPRESS (' any string ');-> ' any string ' mysql> SELECT uncompress (' Any string ');-> Null
Uncompressed_length (compressed_string)
Returns the length of the compressed string before compression.
Mysql> SELECT uncompressed_length (COMPRESS REPEAT (' a ',));-> 30
Unhex (str)
Performs a reverse operation from Hex (str). This means that each pair of hexadecimal digits in the argument is interpreted as a number and converted to the character represented by that number. The resulting character is returned as a binary string.
Mysql> Select Unhex (' 4d7953514c ');-> ' mysql ' mysql> select 0x4d7953514c;-> ' mysql ' mysql> select Unhex ( HEX (' string '));-> ' string ' mysql> SELECT HEX (Unhex (' 1267′));-> ' 1267′
UPPER (str)
Returns the string str and the character converted to uppercase letters based on the latest character set mappings (default is cp1252 Latin1).
mysql> SELECT UPPER (' Hej ');-> ' Hej '
This function supports multibyte characters.

ASCII (str) returns the ASCII code value of the leftmost character of the string str. If Str is an empty string, returns 0. If STR is NULL, returns NULL.
You can also see the Ord () function.
ORD (str) If the string str is the leftmost character is a multibyte character, by using the format ((in the case of the code in the top byte) *256+ (second byte ASCII code) [*256+third byte ASCII code ... Returns the ASCII code value of the character to return the multibyte character code. If the leftmost character is not a multibyte character. Returns the same value returned with the ASCII () function.

CONV (n,from_base,to_base) transforms numbers between different numeric bases.
For example: CONV (15,10,2)
Results: 1111
BIN (n) returns the binary form of n, and N is a long integer (BIGINT) number, which is equivalent to Conv (n,10,2). If n is null, returns NULL.
For example: BIN (15)
Results: 1111
OCT (n) returns the octal form of the number n, where n is a long integer, which is equivalent to Conv (n,10,8). If n is null, returns NULL.
For example: OCT (15)
Results: 17
HEX (n) returns the hexadecimal of number n, where n is a long integer (BIGINT) number, which is equivalent to Conv (n,10,16). If n is null, returns NULL.
For example: HEX (15)
Result: F
CHAR (N,...) CHAR () interprets the argument as an integer and returns a string consisting of the ASCII code characters of these integers. The null value is skipped.
For example: CHAR (97,98,99)
Result: ABC
CONCAT (STR1,STR2,...) Returns a string from the argument's link. Returns null if any argument is null. can have more than 2 parameters. A numeric parameter is transformed into an equivalent string form.
For example: CONCAT (' myname ', ' is ', ' marcofly ')
Result: Mynameismarcofly
Length (str) octet_length (str) char_length (str) character_length (str) returns the lengths of the string str.
For example: LENGTH (' Test ')
Results: 6
Note that for multibyte characters, its char_length () is evaluated only once.
LOCATE (SUBSTR,STR) POSITION (substr in str) returns the substring substr the first occurrence of the string str, if SUBSTR is not inside STR, returns 0.
For example: LOCATE (' name ', ' My name is WHF ')
Results: 4
For example: POSITION (' name ' in ' My name is WHF ')
Results: 4
LOCATE (Substr,str,pos) returns the substring substr the first occurrence of the string str, starting at position pos. If SUBSTR is not inside STR, return 0.
For example: LOCATE (' name ', ' My name is WHF ', 2)
Results: 4
This function is multibyte-reliable.
INSTR (STR,SUBSTR) returns the position of the substring substr the first occurrence in string str. This is the same as the locate () with 2 parameter forms, except that the parameters are reversed. This function is multibyte-reliable.
Lpad (STR,LEN,PADSTR) returns the string str, and the left is filled with a string padstr until Str is len characters long.
For example: Lpad (' Test ', 6, ' 1 ')
Results: 1111 test
Rpad (STR,LEN,PADSTR) returns the string str, and the right is filled with a string padstr until Str is len characters long.
For example: Rpad (' Test ', 6, ' 1 ')
Result: Test 1111

Left (Str,len) returns the leftmost Len character of the string str. (SQL injection is commonly used to guess field names)
For example: Left (' Marcofly ', 5) Results: MARCO
The function is multiple-byte reliable.
Right (Str,len) returns the rightmost Len character of the string str.
For example: Right (' Marcofly ', 3) results: FLY
The function is multiple-byte reliable.
SUBSTRING (Str,pos,len) SUBSTRING (str from POS for len) MID (str,pos,len) returns a substring of Len characters from String str, starting at position pos. The variant form using from is the ANSI SQL92 syntax.
For example: SUBSTRING (' Marcofly ', 6, 3)
Result: Fly
The function is multiple-byte reliable.
SUBSTRING (Str,pos) SUBSTRING (str from POS) returns a substring from the start position pos of the string str.
For example: SUBSTRING (' Marcofly ', 6)
Result: Fly