MySQL string function: String intercept

MySQL string intercept function: Left (), right (), substring (), Substring_index (). There is also mid (), substr (). where mid (), substr () is equivalent to the substring () function, the function of substring () is very powerful and flexible.

1. String intercept: Left (str, length)

Mysql> Select Left (' sqlstudy.com ', 3); +-------------------------+| Left (' sqlstudy.com ', 3) |+-------------------------+| SQL                     |+-------------------------+

2. String intercept: Right (str, length)

Mysql> Select Right (' sqlstudy.com ', 3); +--------------------------+| Right (' sqlstudy.com ', 3) |+--------------------------+| COM                      |+--------------------------+

3. String interception: Substring (str, POS); SUBSTRING (str, POS, len)

3.1 starts at the 4th character position of the string until the end.

mysql> Select substring (' sqlstudy.com ', 4); +------------------------------+| SUBSTRING (' sqlstudy.com ', 4) |+------------------------------+| study.com                    |+------------------------------+

3.2 starts at the 4th character position of the string and takes only 2 characters.

mysql> Select substring (' sqlstudy.com ', 4, 2); +---------------------------------+| SUBSTRING (' sqlstudy.com ', 4, 2) |+---------------------------------+| St                              |+---------------------------------+

3.3 Starts from the 4th character position (reciprocal) of the string until it ends.

mysql> Select substring (' sqlstudy.com ',-4); +-------------------------------+| SUBSTRING (' sqlstudy.com ',-4) |+-------------------------------+|. com                          |+-------------------------------+

3.4 is taken from the 4th character position (reciprocal) of the string and takes only 2 characters.

mysql> Select substring (' sqlstudy.com ', -4, 2); +----------------------------------+| SUBSTRING (' sqlstudy.com ', -4, 2) |+----------------------------------+|. C                               |+--------------------------------- -+

4. String interception: Substring_index (Str,delim,count)

4.1 Intercept all characters before the second '. '.

Mysql> Select Substring_index (' www.sqlstudy.com.cn ', '. ', 2); +------------------------------------------------+ | Substring_index (' www.sqlstudy.com.cn ', '. ', 2) |+------------------------------------------------+| Www.sqlstudy                                   |+------------------------------------------------+

4.2 Intercept all characters after the second '. ' (Countdown).

Mysql> Select Substring_index (' www.sqlstudy.com.cn ', '. ',-2); +------------------------------------------------ -+| Substring_index (' www.sqlstudy.com.cn ', '. ',-2) |+-------------------------------------------------+| com.cn                                          |+-------------------------------------------------+

4.3 If the value specified by the Delim parameter is not found in the string, the entire string is returned

Mysql> Select Substring_index (' www.sqlstudy.com.cn ', '. CoC ', 1); +---------------------------------------------- -----+| Substring_index (' www.sqlstudy.com.cn ', '. CoC ', 1) |+---------------------------------------------------+| www.sqlstudy.com.cn                               |+---------------------------------------------------+

LOCATE (SUBSTR,STR)　

POSITION (substr in str)

Returns the substring substr in the first occurrence of the string str, if SUBSTR is not inside STR, returns 0.

Java code

1.mysql> Select LOCATE (' Bar ', ' Foobarbar ');

2.4

3.mysql> Select LOCATE (' Xbar ', ' foobar ');

4.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 where the first occurrence of the string Str begins, starting at position pos. If the substr is not inside STR, return 0.

Java code

1.mysql> Select LOCATE (' Bar ', ' Foobarbar ', 5);

2.7

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

7

INSTR (STR,SUBSTR)

Returns the position of the first occurrence of a substring substr in the string str. This is the same as the locate () with 2 parameters, except that the parameters are reversed.

Java code

1.mysql> Select INSTR (' Foobarbar ', ' Bar ');

2.4

3.mysql> Select INSTR (' Xbar ', ' foobar ');

4.0

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, left with the string padstr filled until STR is Len characters long.

Java code

1.<pre class= "java" name= "code" >mysql> Select Lpad (' Hi ', 4, '?? ');

2. '?? Hi

3. [Color=darkred]rpad (STR,LEN,PADSTR) [/color] </PRE> return string str, the right side is filled with a string padstr until Str is len characters long.

Java code mysql> Select Lpad (' Hi ', 4, '?? '); '?? Hi

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

'?? Hi

[Color=darkred] Rpad (STR,LEN,PADSTR) [/color]

Returns the string str, to the right with a string padstr until Str is len characters long.

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

' Hi??? '

Left (Str,len)

Returns the leftmost Len character of a string str.

Java code

1.mysql> Select Left (' Foobarbar ', 5);

2. ' Fooba ' This function is multi-byte reliable.

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

' Fooba ' This function is multi-byte reliable.

Right (Str,len)

Returns the rightmost Len character of a string str.

Java code

1.mysql> Select Right (' Foobarbar ', 4);

2. ' Rbar '

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

' Rbar '

The function is multi-byte reliable.

SUBSTRING (Str,pos)

　

SUBSTRING (str from POS)

Returns a substring from the starting position of the string str from 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 the substring that appears after the delimiter Delim from the count of the string str. If Count is a positive number, returns all characters from the last delimiter to the left (from the left). If count 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 the string str whose preceding space character was deleted.

Mysql> Select LTRIM (' Barbar ');

' Barbar '

RTRIM (str)

Returns the string str whose trailing space character was deleted.

Mysql> Select RTRIM (' Barbar ');

' Barbar '

This function is reliable for multiple bytes.

TRIM ([[BOTH | Leading | TRAILING] [REMSTR] from] str)

Returns the string str with all of its remstr prefixes or suffixes removed. If no modifier both, leading, or trailing is given, both is assumed. If REMSTR 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 an homophonic string of str. The 2 strings that sound "roughly the same" should have the same homophonic string. A "standard" of the same character string length is 4 characters, but the SOUNDEX () function returns an arbitrarily long string. You can use SUBSTRING () on the results to 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 a string consisting of n space characters.

Mysql> Select SPACE (6);

""

REPLACE (STR,FROM_STR,TO_STR)

Returns the string str, whose string from_str all occurrences 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 string consisting of the string str that repeats counttimes times. If Count <= 0, an empty string is returned. If STR or count is NULL, returns NULL.

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

' Mysqlmysqlmysql '

REVERSE (str)

Returns a string that reverses the character order of Str.

Mysql> Select REVERSE (' abc ');

' CBA '

This function is reliable for multiple bytes.

INSERT (STR,POS,LEN,NEWSTR)

Returns the string str, a substring starting at position pos, and Len characters long substrings are replaced 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, if n= 2, return str2, and so on. If n is less than 1 or greater than the number of arguments, NULL is returned. ELT () is a 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 str in str1, str2, STR3, ... The index of the manifest. If STR is 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 table strlist consisting of n substrings, returns a value of 1 to N. A string table is a string consisting of "," separated substrings. 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 arithmetic! If STR is not inside strlist or if Strlist is an empty string, return 0. Returns null if any one of the arguments is null. If the first parameter 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 (a string consisting of substrings separated by "," characters), consisting of a string of corresponding bits in the bits collection. The str1 corresponds to bit 0,str2 corresponding 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 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)

Y,n,y,n

LCASE (str)

　

LOWER (str) returns the string str, which changes all characters to lowercase according to 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)

Returns the string str, which 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 content 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 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 only affects comparisons.

MySQL string function: String intercept