Shulou(Shulou.com)06/01 Report--

This article will explain in detail about the string function in mysql, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

The mysql string functions are:

1. LOWER (column | str): converts the string parameter value to all lowercase letters and returns it.

Mysql > select lower ('SQL Course'); +-+ | lower (' SQL Course') | +-+ | sql course | +-+

2. UPPER (column | str): converts the string parameter value to all uppercase letters and returns it.

Mysql > select upper ('Use MYsql'); +-+ | upper (' Use MYsql') | +-+ | USE MYSQL | +-+

3. CONCAT (column | str1, column | str2,...): return after concatenating multiple string parameters from beginning to end

Mysql > select concat ('My','S','QL'); +-- + | concat (' My','S','QL') | +-- + | MySQL | +-- +

If any argument is null, the function returns null

Mysql > select concat ('My',null,'QL'); +-+ | concat (' My',null,'QL') | +-+ | NULL | +-+

If the parameter is a number, it is automatically converted to a string

Mysql > select concat (14.3 recording MySQL'); +-+ | concat (14.3 recording mysql') | +-+ | 14.3mysql | +-+

4. CONCAT_WS (separator,str1,str2,...): multiple string parameters are concatenated with the given delimiter separator and returned

Mysql > select concat_ws (';', 'First name','Second name','Last name'); +-- + | concat_ws (' ',' First name','Second name','Last name') | +-- + | First name;Second name Last name | +-- +

!! That is, the first item in the function parentheses is used to specify the delimiter

5. SUBSTR (str,pos [, len]): take a string from the specified position pos in the source string str and return it

Note:

① len specifies the length of the substring, which is taken to the end of the string if omitted; a negative value of len means it is taken from the end of the source string.

The ② function SUBSTR () is synonymous with the function SUBSTRING ().

Mysql > select substring ('hello world',5) +-- + | substring ('hello world',5) | +-- + | o world | +-+ mysql > select substr (' hello world',5,3) +-- + | substr ('hello world',5,3) | +-+ | o w | +-+ mysql > select substr (' hello world',-5) +-- + | substr ('hello world',-5) | +-- + | world | +-+

6. LENGTH (str): returns the storage length of the string

Mysql > select length ('text'), length (' Hello') +-+-+ | length ('text') | length (' Hello') | +-+ | 4 | 6 | +- -- +

Note: the storage length of strings varies with different encoding methods ('Hello': utf8 is 6 and Gbk is 4)

7. CHAR_LENGTH (str): returns the number of characters in a string

Mysql > select char_length ('text'), char_length (' Hello') +-+-+ | char_length ('text') | char_length (' Hello') | +-+-- + | 4 | 2 | +-+-- +

8. INSTR (str, substr): returns the location where the substring substr first appeared from the source string str

Mysql > select instr ('foobarbar','bar'); +-+ | instr (' foobarbar','bar') | +-+ | 4 | +-+

9. LPAD (str, len, padstr): fill the left side of the source string with the given character padstr to the specified length len, and return the filled string

Mysql > select lpad ('hi',5,'??'); +-+ | lpad (' hi',5,'??') | +-+ |? hi | +-+

10. RPAD (str, len, padstr): fill the given character padstr to the specified length len on the right side of the source string, and return the filled string

Mysql > select rpad ('hi',6,'??'); +-+ | rpad (' hi',6,'??') | +-+ | hi???? | +-+

11. TRIM ([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM ([remstr FROM] str):

Removes both ends, prefix or suffix characters remstr from the source string str and returns

If you do not specify remstr, the spaces at both ends of the str are removed; if you do not specify BOTH, LEADING, and TRAILING, the default is BOTH.

Mysql > select trim ('bar'); +-+ | trim ('bar') | +-+ | bar | +-+ mysql > select trim (leading'x 'from' xxxbarxxx') +-- + | trim (leading'x 'from' xxxbarxxx') | +-+ | barxxx | +- -+ mysql > select trim (both 'x' from 'xxxbarxxx') +-- + | trim (both'x 'from' xxxbarxxx') | +-- + | bar | + -+ mysql > select trim (trailing 'xyz' from' barxxyz') +-- + | trim (trailing 'xyz' from' barxxyz') | +-+ | barx | +- -- +

12. REPLACE (str, from_str, to_str): look for all substrings form_str (case-sensitive) in the source string str, and replace it with the replacement string to_str. Returns the replaced string

Mysql > select replace ('www.mysql.com','w','Ww') +-- + | replace ('www.mysql.com','w' 'Ww') | +-+ | WwWwWw.mysql.com | +-+

13. LTRIM (str), RTRIM (str): remove the left or right space of the string (left-aligned, right-aligned)

Mysql > SELECT ltrim ('barbar') rs1, rtrim ('barbar') rs2;+-+-+ | rs1 | rs2 | +-+-+ | barbar | barbar | +-+-+

14. REPEAT (str, count): return the string str after repeating it for count times

Mysql > select repeat ('MySQL',3); +-+ | repeat (' MySQL',3) | +-+ | MySQLMySQLMySQL | +-+

15. REVERSE (str): reverses the string str and returns

Mysql > select reverse ('abcdef'); +-+ | reverse (' abcdef') | +-+ | fedcba | +-+

16. CHAR. [USING charset_name]): interprets each parameter N as an integer (character encoding) and returns a string of characters corresponding to each integer (null value is ignored).

Mysql > select char (77, 121, 83, 81, 76'), char (77, 77.3, 77.3) +-+-+ | char (77pencils 121pr 83pr 81rem 76') | char (77pr 77.3) '77.3') | +-+-+ | MySQL | MMM | +-+-- +

By default, the function returns binary strings, and if you want to return strings for a specific character set, use the using option

Mysql > SELECT charset (char (0x65)), charset (char (0x65 USING utf8)) +-+ | charset (char (0x65)) | charset (char (0x65 USING utf8)) | + -+ | binary | utf8 | +-- +

17. FORMAT (XQuery D [, locale]): format the numeral X in the format'#, #, #. # #'

D specify number of decimal places

Locale specifies the national language (the default locale is en_US)

Mysql > SELECT format (12332.123456, 4), format (12332.2); +-+

| | format (12332.123456, 4) | format (12332.2) |

+-+ +

| | 12332.1235 | 12332 | |

+-+-+ mysql > SELECT format (12332.2); +-+

| | format (12332.2), which is called "12332.2" and "2pr" deactivated DE') |

+-+

| | 12.332 Jing 20 |

+-+

18. SPACE (N): returns a string of N spaces

Mysql > select space (3); +-+ | space (3) | +-+ | | +-+

19. LEFT (str, len): returns the leftmost substring of len length

Mysql > select left ('chinaitsoft',5); +-- + | left (' chinaitsoft',5) | +-- + | china | +-- +

20. RIGHT (str, len): returns the rightmost len length substring

Mysql > select right ('chinaitsoft',5); +-+ | right (' chinaitsoft',5) | +-+ | tsoft | +-+

21. STRCMP (expr1,expr2): returns 0 if two strings are the same;-1 if the first is less than the second; otherwise, 1

Mysql > select strcmp ('text','text') +-- + | strcmp ('text','text') | +-- + | 0 | +-- + mysql > SELECT strcmp (' text',' text2'), strcmp ('text2',' text') +-+ | strcmp ('text',' text2') | strcmp ('text2' 'text') | +-+-+ |-1 | 1 | +-+- -+ this is the end of the introduction to string functions in mysql I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.