Mysql you do not know the string operation function

This article will give you a detailed introduction to mysql. If you do not know string operation functions, you can refer to them.

 

The LENGTH () function can obtain the LENGTH of a string:

Select length ('abcdefg'), length ('20140901 ');
/*
+ ------------------- + ---------------------- +
| Length ('abcdefg') | length ('20140901') |
+ ------------------- + ---------------------- +
| 7 | 10 |
+ ------------------- + ---------------------- +
1 row in set (0.00 sec)
*/

You can use the RPAD () or LPAD () function to fill the string from the right or left:

In general, strings are often filled with spaces (that is, the so-called completion). MySQL can provide RPAD () and LPAD () specify a custom value in the function as the third parameter to fill the string.

Select rpad ('simaopig ', 17, ''), rpad ('simaopig', 17, '@'), lpad ('simaopig ', 17 ,''), 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 ', 17, ''), rpad ('simaopig', 17, '@'), lpad ('simaopig ', 17 ,''), lpad ('simaopig ', 17 ,'@');
/*
+ ------------------------- +
| Rpad ('simaopig ', 17, '') | rpad ('simaopig', 17, '@') | lpad ('simaopig ', 17 ,'') | lpad ('simaopig ', 17,' @ ') |
+ ------------------------- +
| Simaopig | @ simaopig |
+ ------------------------- +
1 row in set (0.00 sec)
*/

The LTRIM () and RTRIM () functions have the opposite effect of filling, removing the first and last characters of the string:

Select ltrim ('simaopig '), ltrim ('simaopig'), rtrim ('simaopig '), rtrim ('simaopig'), ltrim (rtrim ('simaopig '));
/*
+ ---------------------- + --------------------------- + ---------------------- + ---------------------------- + -------------------------------- +
| Ltrim ('simaopig ') | rtrim ('simaopig') | ltrim (rtrim ('simaopig ') |
+ ---------------------- + --------------------------- + ---------------------- + ---------------------------- + -------------------------------- +
| Simaopig |
+ ---------------------- + --------------------------- + ---------------------- + ---------------------------- + -------------------------------- +
1 row in set (0.00 sec)
*/

When we talk about "SPACE", copy the example in the book again, that is, MySQL provides a function. The function of this function (SPACE () only returns spaces.

Select space (1), space (4), length (space (1), length (space (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 format to be removed, and can specify whether to remove the left or right? Or remove other characters (not limited to spaces ):

Actually, we have done both the RTRIM () and LTRIM () jobs and done well. We can say that TRIM () and RTRIM () are a subset 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 |
+ -------------- + --------------- + ----------- +
1 row in set (0.00 sec)
*/

The LEFT () and RIGHT () functions obtain the specified part of the string, which returns characters from the LEFT or RIGHT 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 allows you to specify the length and start position of a string. It is also used to obtain the SUBSTRING:

Note that the starting position of the MySQL string here is 1, not zero.

Select substring ('simaopig ', 2, 3 );
/*
+ --------------------------- +
| Substring ('simaopig ', 2, 3) |
+ --------------------------- +
| Ima |
+ --------------------------- +
1 row in set (0.00 sec)
*/

The CONCAT () function Concatenates the parameters provided in it:

If the CONCAT () parameter is NULL, the result is also NULL ).

Select concat ('Welcome ', 'to', 'lower sub'), concat ('simaopig', NULL );
/*
+ ------------------------------------ + ------------------------- +
| Concat ('Welcome ', 'to', 'lower sub') | concat ('simaopig', NULL) |
+ ------------------------------------ + ------------------------- +
| Welcometo splitter | NULL |
+ ------------------------------------ + ------------------------- +
1 row in set (0.00 sec)
*/

The CONCAT_WS () function is basically the same as the CONCAT () function, but it can also provide a parameter as the separator of the connected 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 check whether a string contains the string to be detected. If yes, return the location where it first appears:

Today, I would like to emphasize for the second time. The starting position of the MySQL string is 1, not zero.

Select locate ('pig', 'simaopig '), locate ('', 'simaopig ');
/*
+ -------------------------- + ----------------------- + ------------------------ + -------------------------- +
| Locate ('pig', 'simaopig ') | locate ('', 'simaopig ') |
+ -------------------------- + ----------------------- + ------------------------ + -------------------------- +
| 6 | 1 | 0 | 1 |
+ -------------------------- + ----------------------- + ------------------------ + -------------------------- +
1 row in set (0.00 sec)
*/

The FIND_IN_SET () function is used to find the specified string in a large collection:

When we see set in MySQL, we 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, you can see that the returned string is the first element of the set.

Why are the results returned in uppercase? Well, I didn't use binary.

The STRCMP () function checks whether two strings are the same. If the first string is greater than the second string, return 1; otherwise, return-1:

Er, if binary is not added, string comparison is 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 used in the same way as other programming languages. The replacement function is as follows:

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 can replace a specified part of a string (defining the start 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 'xiaoxiaozi' 10 times'

Select repeat ('xiaoxiaozi', 10 );
/*
+ Shards +
| Repeat ('xiaoxiaozi', 10) |
+ Shards +
| Bytes |
+ Shards +
1 row in set (0.00 sec)
*/

The REVERSE string of the REVERSE () function:

Select reverse ('simaopig '), reverse (repeat ('hello', 6 ));
/*
+ --------------------- + ---------------------------------------- +
| Reverse ('simaopig ') | reverse (repeat ('hello', 6) |
+ --------------------- + ---------------------------------------- +
| Gipoamis | olleh |
+ --------------------- + ---------------------------------------- +
1 row in set (0.00 sec)
*/

UCASE () and LCASE () convert strings into uppercase and lowercase letters respectively:

Select ucase ('simaopig '), ucase ('simaopig'), lcase ('simaopig '), lcase ('simaopig ');
/*
+ ------------------- +
| Ucase ('simaopig ') | lcase ('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 ') |
+ ------------ + ------------------- +
| 121 | 83 | 115 |
+ ------------ + ------------------- +
1 row in set (0.00 sec)
*/

The ORD () function returns the numeric encoding of a specified character, which is often used to replace ASCII ():

Well, I think its return value is exactly the same as ASCII. PHP also has this function, which looks like.

Select ord ('y'), ord ('simaopig '), ord ('simaopig ');
/*
+ ---------- + ----------------- +
| Ord ('y') | ord ('simaopig ') |
+ ---------- + ----------------- +
| 121 | 83 | 115 |
+ ---------- + ----------------- +
1 row in set (0.00 sec)
*/

Add more

1. ASCII (str)
Returns the ASCII code value of the leftmost character of the str string. If str is a Null String, 0 is returned. If 'str' is NULL, return NULL.
Mysql> select ASCII ('2 ');
-> 50
Mysql> select ASCII (2 );
-> 50
Mysql> select ASCII ('dx ');
-> 100 for more information, see the ORD () function.

2. ORD (str)
If the leftmost character of a string 'str' is a multi-byte character, use the format (first byte ASCII code) * 256 + (second byte ASCII code )) [* 256 + third byte ASCII code...] returns the ASCII code value of a character to return the multi-byte code. If the leftmost character is not a multi-byte character. Returns the same value as that returned by the ASCII () function.
Mysql> select ORD ('2 ');
-> 50
3. CONV (N, from_base, to_base)
Convert numbers between different digit bases. Returns the string Number of number N, which is converted from from_base base to to_base base. If any parameter is NULL, NULL is returned. Parameter N is interpreted as an integer, but can be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is considered as a signed number. Otherwise, N is considered as an unsigned number. CONV works with 64-bit precision.
Mysql> select CONV ("a", 16, 2 );
-> '123'
Mysql> select CONV ("6E", 18, 8 );
-> '123'
Mysql> select CONV (-17,10,-18 );
-> '-H'
Mysql> select CONV (10 + "10" + '10' + 0xa, 10, 10 );
-> '40'
4. BIN (N)
Returns a string of the binary value N. Here N is a long integer (BIGINT) number, which is equivalent to CONV (N, 10, 2 ). If N is NULL, return NULL.
Mysql> select BIN (12 );
-> '123'
5. OCT (N)
Returns the representation of a string with an octal value N. Here N is a long integer, which is equivalent to CONV (N, 10, 8 ). If N is NULL, return NULL.
Mysql> select OCT (12 );
-> '14'
6. HEX (N)
Returns the representation of a hexadecimal value N string. Here N is a long integer (BIGINT) number, which is equivalent to CONV (N, 10, 16 ). If N is NULL, return NULL.
Mysql & gt; select HEX (255 );
-> 'Ff'
7. CHAR (N ,...)
CHAR () interprets the parameter as an integer and returns a string consisting of ASCII code characters of these integers. The NULL value is skipped.
Mysql> select CHAR (77,121, 81, '76 ');
-> 'Mysql'
Mysql> select CHAR (77, 77.3, '77. 3 ');
-> 'Mmm'