MySQL you don't know string manipulation functions

The length () function can get the lengths of a string:

Select Length (' ABCDEFG '), Length (' 0123456789 ');
/*
+-------------------+----------------------+
| Length (' ABCDEFG ') | Length (' 0123456789 ') |
+-------------------+----------------------+
| 7 | 10 |
+-------------------+----------------------+
1 row in Set (0.00 sec)
*/

You can use the Rpad () or Lpad () function to fill strings from the right or left side, respectively:

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 (' Simaopig '));
/*
+----------------------+---------------------------+----------------------+--------------------------+--------- -------------------------+
| LTrim (' Simaopig ') | LTrim (' Simaopig ') | RTrim (' Simaopig ') | RTrim (' Simaopig ') | LTrim (RTrim (' Simaopig ')) |
+----------------------+---------------------------+----------------------+--------------------------+--------- -------------------------+
| Simaopig | Simaopig | Simaopig | Simaopig | Simaopig |
+----------------------+---------------------------+----------------------+--------------------------+--------- -------------------------+
1 row in Set (0.00 sec)
*/

That is to say "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 can get the specified portion of the string, which returns the character from the left-hand 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 you want to detect and, if it exists, 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)
*/

Ah, see, it returns the string is the first element 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)
*/

Just to add

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

2.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
3.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 '
4.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 '
5.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 '
6.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 '
7.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 '