MySQL String function Summary

() function to get the length of a string:


Select Length (' ABCDEFG '), Length (' 0123456789 ');


/*


+-------------------+----------------------+


| Length (' ABCDEFG ') | Length (' 0123456789 ') |


+-------------------+----------------------+


|                   7 | 10 |


+-------------------+----------------------+


1 row in Set (0.00 sec)


* * Use the Rpad () or Lpad () function to fill a string from the right or left side:


In general, strings are often filled with blanks (so-called padded), and MySQL can fill strings by specifying a custom value as the third parameter in the Rpad () and Lpad () functions.


Select Rpad (' Simaopig ', D, '), Rpad (' Simaopig ', @, ' @ '), Lpad (' Simaopig ', ', '), Lpad (' Simaopig ', 17, ' @ ');


/*


+------------------------+-------------------------+------------------------+-------------------------+


| Rpad (' Simaopig ', 17, ') | Rpad (' Simaopig ', 17, ' @ ') | Lpad (' Simaopig ', 17, ') | Lpad (' Simaopig ', 17, ' @ ') |


+------------------------+-------------------------+------------------------+-------------------------+


| NULL | simaopig@@@@@@@@@ | NULL | @simaopig |


+------------------------+-------------------------+------------------------+-------------------------+


1 row in Set (0.00 sec)


*/


Select Rpad (' Simaopig ', D, '), Rpad (' Simaopig ', @, ' @ '), Lpad (' Simaopig ', ', '), Lpad (' Simaopig ', 17, ' @ ');


/*


+-------------------------+-------------------------+-------------------------+-------------------------+


| Rpad (' Simaopig ', 17, ') | Rpad (' Simaopig ', 17, ' @ ') | Lpad (' Simaopig ', 17, ') | Lpad (' Simaopig ', 17, ' @ ') |


+-------------------------+-------------------------+-------------------------+-------------------------+


| Simaopig |          simaopig@@@@@@@@@ | Simaopig | @simaopig |


+-------------------------+-------------------------+-------------------------+-------------------------+


1 row in Set (0.00 sec)


The */ltrim () and RTrim () functions produce the opposite effect, removing the first and trailing characters of the string:


Select LTrim (' Simaopig '), LTrim (' Simaopig '), RTrim (' Simaopig '), RTrim (' Simaopig '), LTrim (' RTrim Pig '));


/*


+----------------------+---------------------------+----------------------+--------------------------+--------- -------------------------+


| LTrim (' Simaopig ') | LTrim (' Simaopig ') | RTrim (' Simaopig ') | RTrim (' Simaopig ') | LTrim (RTrim (' Simaopig ')) |


+----------------------+---------------------------+----------------------+--------------------------+--------- -------------------------+


| Simaopig | Simaopig |        Simaopig | Simaopig | Simaopig |


+----------------------+---------------------------+----------------------+--------------------------+--------- -------------------------+


1 row in Set (0.00 sec)


* * Then talked about the "space", then copy the example in the book again, that is, MySQL provides a function, this function (space ()) function is to return only spaces


Select Space (1), spaces (4), Length (space (1)), Length (spaces (4));


/*


+----------+----------+------------------+------------------+


| Space (1) | Space (4) | Length (Space (1)) | Length (Space (4)) |


+----------+----------+------------------+------------------+


|          |                |                1 | 4 |


+----------+----------+------------------+------------------+


1 row in Set (0.00 sec)


The */trim () function can specify the removal format, and can specify whether to remove the left or right side? or remove other characters (not limited to spaces):


Actually, it's all done with RTrim () and LTrim (), and it's good to say that trim () and RTrim () are subsets of trim () ...


Select Trim (' Simaopig '), Length (Trim (' Simaopig ')) as Len;


/*


+--------------------+-----+


| Trim (' Simaopig ') | Len |


+--------------------+-----+


|   Simaopig | 8 |


+--------------------+-----+


1 row in Set (0.00 sec)


*/


Select trim (Leading '! ' FROM '!!! Simaopig!!! ') As Trim_leading,trim (trailing '! ' FROM '!!! Simaopig!!! ') As Trim_trailing,trim (both '! ' FROM '!!! Simaopig!!! ') As Trim_both;


/*


+--------------+---------------+-----------+


| trim_leading | trim_trailing | Trim_both |


+--------------+---------------+-----------+


|  Simaopig!!! | !!! Simaopig | Simaopig |


+--------------+---------------+-----------+


1 row in Set (0.00 sec)


The */left () and right () functions get the specified portion of the string, which returns the character from the left or right-hand side of the string:


Select Left (' Simaopig ', 5) as Left_five,right (' Simaopig ', 5) as Right_five,left (right (' Simaopig ', 7), 5) as midd_five;


/*


+-----------+------------+-----------+


| left_five | right_five | midd_five |


+-----------+------------+-----------+


| Simao | Aopig | IMAOP |


+-----------+------------+-----------+


1 row in Set (0.00 sec)


The */substring () function, which allows you to specify the length and starting position of a string, and also to get a substring:


Note that the MySQL string starting position here is 1, not 0 ha.


Select substring (' Simaopig ', 2, 3);


/*


+---------------------------+


| SUBSTRING (' Simaopig ', 2, 3) |


+---------------------------+


| Ima |


+---------------------------+


1 row in Set (0.00 sec)


The */concat () function joins the parameters provided therein:


If the argument for concat () is empty, the result is empty (null).


Select Concat (' Welcome ', ' to ', ' Keoko '), concat (' Simaopig ', NULL);


/*


+------------------------------------+-------------------------+


| Concat (' Welcome ', ' to ', ' Keoko ') | Concat (' Simaopig ', NULL) |


+------------------------------------+-------------------------+


| Welcometo Keoko | NULL |


+------------------------------------+-------------------------+


1 row in Set (0.00 sec)


The */concat_ws () function is essentially the same as the CONCAT () function, except that it can also provide a parameter as a delimiter for the concatenated string:


Select Concat_ws (', ', ' Welcome ', ' to ', ' xiaoxiaozi.com ') as Con_wel,concat_ws (", ' Simaopig ', ' love ', ' Yatou ') as Con_ Love


/*


+---------------------------+---------------------+


| Con_wel | Con_love |


+---------------------------+---------------------+


| welcome,to,xiaoxiaozi.com | Simaopig Love Yatou |


+---------------------------+---------------------+


1 row in Set (0.00 sec)


* * Use the Locate () function to verify that a string contains the string to be detected and, if so, to return its first occurrence:


Today, for the second time. The MySQL string starts at 1, not zero.


Select locate (' Pig ', ' simaopig '), locate (', ' Simaopig '), locate (', ' Simaopig '), locate (', ' simaopig ');


/*


+--------------------------+-----------------------+------------------------+--------------------------+


| Locate (' pig ', ' simaopig ') | Locate (', ' Simaopig ') | Locate (', ' Simaopig ') | Locate (', ' Simaopig ') |


+--------------------------+-----------------------+------------------------+--------------------------+


|                     6 |                      1 |                        0 | 1 |


+--------------------------+-----------------------+------------------------+--------------------------+


1 row in Set (0.00 sec)


The */find_in_set () function finds the specified string in a large collection:


When you see set in MySQL, you should think of a set!!!


Select Find_in_set (' Simaopig ', ' My,name,is,simaopig,you,can,call,me,simaopig,too ') as string_locate;


/*


+---------------+


| String_locate |


+---------------+


| 4 |


+---------------+


1 row in Set (0.00 sec)


* * ha, see, it returns the string is the first few elements of the collection, um.


And you asked why the capitalization came back? Well, because I didn't use binary.


The STRCMP () function compares two strings to the same, returns 0, or returns 1 if the first is greater than the second returns 1:


Well, similarly, without binary, string comparisons are case-insensitive.


Select strcmp (' A ', ' B '), strcmp (' B ', ' a '), strcmp (' A ', ' a '), strcmp (' A ', ' a '), strcmp (' A ', ' B ');


/*


+-----------------+-----------------+-----------------+-----------------+-----------------+


| strcmp (' A ', ' B ') | strcmp (' B ', ' A ') | strcmp (' A ', ' a ') | strcmp (' A ', ' a ') | strcmp (' A ', ' B ') |


+-----------------+-----------------+-----------------+-----------------+-----------------+


|               -1 |               1 |               0 |              0 | -1 |


+-----------------+-----------------+-----------------+-----------------+-----------------+


1 row in Set (0.00 sec)


The */replace () function is consistent with the usage in other programming languages, replacing the function:


Select replace (' I am simaopig ', ' simaopig ', ' Xiaoxiaozi ');


/*


+--------------------------------------------------+


| Replace (' I am simaopig ', ' simaopig ', ' Xiaoxiaozi ') |


+--------------------------------------------------+


| I am Xiaoxiaozi |


+--------------------------------------------------+


1 row in Set (0.00 sec)


The */inssert () function replaces a specified portion of a string (defined starting position and length) with a new value:


Select Insert (' I am simaopig ', 6,8, ' Xiaoxiaozi ');


/*


+------------------------------------------+


| Insert (' I am simaopig ', 6,8, ' Xiaoxiaozi ') |


+------------------------------------------+


| I am Xiaoxiaozi |


+------------------------------------------+


1 row in Set (0.00 sec)


The */repeat () function is used to repeat string operations:


For example: Output 10 times ' Xiaoxiaozi '


Select repeat (' Xiaoxiaozi ', 10);


/*


+------------------------------------------------------------------------------------------------------+


| Repeat (' Xiaoxiaozi ', 10) |


+------------------------------------------------------------------------------------------------------+


| Xiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozi |


+------------------------------------------------------------------------------------------------------+


1 row in Set (0.00 sec)


The */reverse () function reverses the string:


Select reverse (' Simaopig '), Reverse (repeat (' Hello ', 6));


/*


+---------------------+--------------------------------------+


| Reverse (' Simaopig ') | Reverse (Repeat (' Hello ', 6)) |


+---------------------+--------------------------------------+


|  Gipoamis | Olleh Olleh olleh Olleh Olleh Olleh |


+---------------------+--------------------------------------+


1 row in Set (0.00 sec)


*/ucase () and LCase () convert strings to uppercase and lowercase respectively:


Select UCase (' Simaopig '), UCase (' Simaopig '), LCase (' Simaopig '), LCase (' Simaopig ');


/*


+-------------------+-------------------+-------------------+-------------------+


| UCase (' Simaopig ') | UCase (' Simaopig ') | LCase (' Simaopig ') | LCase (' Simaopig ') |


+-------------------+-------------------+-------------------+-------------------+


| Simaopig | Simaopig | Simaopig | Simaopig |


+-------------------+-------------------+-------------------+-------------------+


1 row in Set (0.00 sec)


The */ASCII () function returns the ASCII code of the specified character:


Select ASCII (' Y '), ASCII (' Simaopig '), ASCII (' Simaopig ');


/*


+------------+-------------------+-------------------+


| ASCII (' Y ') | ASCII (' Simaopig ') | ASCII (' Simaopig ') |


+------------+-------------------+-------------------+


|                121 |               83 | 115 |


+------------+-------------------+-------------------+


1 row in Set (0.00 sec)


The */ord () function returns the numeric encoding of the specified character, often used in place of ASCII ():


Well, how I feel. The return value is exactly the same as ASCII (). PHP also has this function, seemingly.


Select Ord (' Y '), ord (' Simaopig '), Ord (' Simaopig ');


/*


+----------+-----------------+-----------------+


| Ord (' Y ') | Ord (' Simaopig ') | Ord (' Simaopig ') |


+----------+-----------------+-----------------+


|              121 |             83 | 115 |


+----------+-----------------+-----------------+


1 row in Set (0.00 sec)


*/

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

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.
Mysql> Select ASCII (' 2 ');        -> 50mysql> Select ASCII (2);        -> 50mysql> Select ASCII (' DX '); -> 100
You can also see the Ord () function.

ORD (str)


If the string str leftmost character is a multibyte character, by using the format (the *256+ (the 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.


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


CONV (N,from_base,to_base)


Transforms a number between different numeric bases. Returns a string number for the number n, from the From_base base transformation to the to_base base, or null if any argument is null. Parameter n is interpreted as an integer, but can be specified as an integer or a string. The minimum base is 2 and the largest base is 36. If To_base is a negative number, n is considered to be a signed digit, otherwise n is treated as unsigned. Conv work 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+ "ten" + ' +0xa,10,10 '); -> ' 40 '


BIN (N)


Returns a string representation of the binary value N, where n is a long integer (BIGINT) number, which is equivalent to Conv (n,10,2). If n is null, returns NULL.


Mysql> Select BIN (12); -> ' 1100 '


OCT (N)


Returns the representation of a string of octal value N, where n is a long integer number, which is equivalent to Conv (n,10,8). If n is null, returns NULL.


Mysql> Select OCT (12); -> ' 14 '


HEX (N)


Returns the hexadecimal value n the representation of a string, where n is a long integer (BIGINT) number, which is equivalent to Conv (n,10,16). If n is null, returns NULL.


Mysql> Select HEX (255); -> ' FF '


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.


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. Returns null if any argument is 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 ');        -> nullmysql> Select CONCAT (14.3); -> ' 14.3 '


LENGTH (str)


　


Octet_length (str)


　


Char_length (str)


　


Character_length (str)


Returns the length of the string str.


Mysql> Select LENGTH (' text ');        -> 4mysql> 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 the first occurrence of the string str, if SUBSTR is not inside STR, returns 0.
Mysql> Select LOCATE (' Bar ', ' Foobarbar ');        -> 4mysql> Select LOCATE (' Xbar ', ' foobar '); -> 0
The function is multiple-byte reliable.
LOCATE (Substr,str,pos)
Returns the substring substr the position of the first occurrence of the string str, starting at the location POS. If SUBSTR is not inside STR, return 0.
Mysql> Select LOCATE (' Bar ', ' Foobarbar ', 5); -> 7
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.
Mysql> Select INSTR (' Foobarbar ', ' Bar ');        -> 4mysql> Select INSTR (' Xbar ', ' foobar '); -> 0
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.
Mysql> Select Lpad (' Hi ', 4, '?? '); -> '?? Hi
Rpad (STR,LEN,PADSTR)
Returns the string str, and the right is filled 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 the string str.
Mysql> Select Left (' Foobarbar ', 5); -> ' Fooba '
The function is multiple-byte reliable.

Right (Str,len)
Returns the rightmost Len character of the string str.
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)
Returns a substring of Len characters from String str, starting at position pos. The variant form using from 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)
Returns a substring from the starting position of string Str.
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 of Delim after the occurrence of the delimiter from the count of string str. If Count is a positive number, returns all characters from the last separator to the left (from the left). If count 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 the string str that deleted its preceding space character.
Mysql> Select LTRIM ('   barbar ');       -> ' Barbar '
RTRIM (str) The
returns the string str whose trailing space character has been deleted.
Mysql> Select RTRIM (' barbar   ');       -> ' Barbar '
This function is reliable for multiple bytes.  
TRIM ([BOTH | Leading | Trailing] [REMSTR] from] str
Returns the string str, all of its remstr prefixes or suffixes are removed. If no modifiers both, leading, or trailing are given, the both is assumed. If the 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 '
The function is reliable for multibyte.

SOUNDEX (str)
Returns a homonym string for str. 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 SUBSTRING () in the results to 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 a string consisting of n white space characters.
Mysql> Select Space (6);       -> '       '
Replace (STR,FROM_STR,TO_STR)
Returns the string str, where all occurrences of the string from_str are replaced by string to_str.
Mysql> Select REPLACE (' www.mysql.com ', ' w ', ' Ww ');       -> ' WwWwWw.mysql.com '
This function is reliable for multibyte.

REPEAT (Str,count)
Returns a string of string str that is repeated counttimes times. If Count <= 0, returns an empty string. Returns null if STR or count is null.
Mysql> Select REPEAT (' MySQL ', 3); -> ' Mysqlmysqlmysql '
REVERSE (str)
Returns the string str of the reversed character order.
Mysql> Select REVERSE (' abc '); -> ' CBA '
This function is reliable for multiple bytes.

INSERT (STR,POS,LEN,NEWSTR)
Returns the string str, the substring at the beginning of the position Pos and the Len character long substring is replaced by the 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. Returns NULL if n is less than 1 or greater than the number of arguments. ELT () is a field () inverse operation.


Mysql&gt; Select ELT (1, ' ej ', ' Heja ', ' Hej ', ' foo ');        -&gt; ' EJ ' mysql&gt; select ELT (4, ' ej ', ' Heja ', ' Hej ', ' foo '); -&gt; ' foo '


FIELD (STR,STR1,STR2,STR3,...)


Back to Str in str1, str2, STR3, ... The index of the manifest. If STR is not found, return 0. FIELD () is an ELT () inverse operation.


Mysql&gt; Select FIELD (' EJ ', ' Hej ', ' ej ', ' Heja ', ' Hej ', ' foo ');        -&gt; 2mysql&gt; Select FIELD (' fo ', ' Hej ', ' ej ', ' Heja ', ' Hej ', ' foo '); -&gt; 0


Find_in_set (Str,strlist)


If string str returns a value of 1 to N in the table strlist composed of n substrings. 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 and the bitwise operation is used! If STR is not inside the strlist or if Strlist is an empty string, return 0. Returns null if any one of the arguments is null. If the first argument contains a "," the function will not work correctly.


mysql&gt; SELECT find_in_set (' B ', ' a,b,c,d '); -&gt; 2


Make_set (BITS,STR1,STR2,...)


Returns a collection containing a string of substrings separated by the "," character, consisting of a string of corresponding bits in the bits collection. STR1 corresponds to bit 0,str2 corresponding bit 1, and so on. In str1, str2, ... The null string in is not added to the result.


Mysql&gt; SELECT make_set (1, ' A ', ' B ', ' C ');        -&gt; ' A ' mysql&gt; SELECT make_set (1 | 4, ' hello ', ' nice ', ' world ');        -&gt; ' Hello,world ' mysql&gt; SELECT make_set (0, ' a ', ' B ', ' C '); -&gt; '


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&gt; Select Export_set (5, ' Y ', ' N ', ', ', 4)-&gt; 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 (default is Iso-8859-1 Latin1). This function is reliable for multiple bytes.


Mysql&gt; Select LCASE (' quadratically '); -&gt; ' quadratically '


UCASE (str)


　


UPPER (str)


Returns the string str, which 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&gt; Select UCASE (' Hej '); -&gt; ' 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 null if the file does not exist or because one of the above reasons cannot be read out.
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";        -> 2mysql> 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 the 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.