Mysql you do not know the string operation function

Source: Internet
Author: User
Tags rtrim

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'
 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.