Home >Database >Mysql Tutorial >What are the mysql string functions?

What are the mysql string functions?

coldplay.xixi
coldplay.xixiOriginal
2020-06-29 11:37:254862browse

mysql string functions include: 1. LOWER, convert the string parameter value to all lowercase letters and return; 2. UPPER, convert the string parameter value to all uppercase letters and return; 3. CONCAT, Return multiple string parameters concatenated end to end; 4. SUBSTR, starting from the specified position pos in the source string str.

What are the mysql string functions?

mysql string functions are:

What are the mysql string functions?

1, LOWER(column|str): Convert the string parameter value to all lowercase letters and return

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

2, UPPER(column| str): Convert the string parameter value to all uppercase letters and return

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

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

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

If any parameter is null, the function returns null

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

If the parameter is a number, Then it will be automatically converted into a string

mysql> select concat(14.3,'mysql');+----------------------+
| concat(14.3,'mysql') |
+----------------------+
| 14.3mysql            |
+----------------------+

4. CONCAT_WS(separator,str1,str2,...):Convert multiple string parameters to the given The separator separator is connected end to end and returns

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 separator

5, SUBSTR(str,pos[,len]): From the specified position in the source string str pos starts taking a string and returns

Note:

①len specifies the length of the substring. If omitted, it will be taken to the end of the string; a negative value of len means starting from the source string The tail begins to pick up.

 ②Function SUBSTR() is a synonym of 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('你好');+----------------+------------------+
| length('text') | length('你好')   |
+----------------+------------------+
|              4 |                6 |
+----------------+------------------+

Note: The storage length of the string depends on the encoding method. Different ('Hello': utf8 is 6, gbk is 4)

7, CHAR_LENGTH(str): Return string The number of characters in

mysql> select char_length('text'),char_length('你好');+---------------------+-----------------------+
| char_length('text') | char_length('你好')   |
+---------------------+-----------------------+
|                   4 |                     2 |
+---------------------+-----------------------+

8. INSTR(str, substr): Return the substring substr from the source string str The position of an occurrence

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

9. LPAD(str, len, padstr): Padding the given left side of the source string 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): Padding the given character padstr to the specified length len on the right side of the source string, and returning the filled string

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


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

Remove both ends, prefixes or The suffix character remstr is returned;

If remstr is not specified, the spaces at both ends of str will be removed; if BOTH, LEADING, and TRAILING are not specified, 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): Find all substrings form_str (case sensitive) in the source string str, and find them Replace it with the replacement string to_str. Return 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 side of the string Spaces (left justified, right justified)

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

14. REPEAT(str, count): Repeat the string str count times Then return

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

15. REVERSE(str): Reverse the string str and return

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

##16, CHAR(N,... [USING charset_name]): Interpret each parameter N as an integer (character encoding), and return each A string consisting of characters corresponding to an integer (NULL values ​​are ignored).

mysql> select char(77,121,83,81,'76'),char(77,77.3,'77.3');+-------------------------+----------------------+
| char(77,121,83,81,'76') | char(77,77.3,'77.3') |
+-------------------------+----------------------+
| MySQL                   | MMM                  |
+-------------------------+----------------------+

By default, the function returns a binary string. If you want to return a string 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(X,D[,locale]): Format the number X

  • in the format '#,

    ,

    .##' D specifies the number of decimal places
  • locale specifies the national language (the default locale is en_US)


mysql> SELECT format(12332.123456, 4), format(12332.2,0); ---------------------------------- ------------------ -
| format(12332.123456, 4) | format(12332.2,0) |
----------------------- --- ----------------
| 12,332.1235 | 12,332 |
----------------------- ------------------- mysql> SELECT format(12332.2,2,'de_DE'); --------------- ------------
| format(12332.2,2,'de_DE') |
-------------------- -------
| 12.332,20                 |
---------------------------

18. SPACE(N):

Returns a string consisting of N spaces

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

###

19、LEFT(str, len):返回最左边的len长度的子串

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

 

20、RIGHT(str, len):返回最右边的len长度的子串

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

 

21、STRCMP(expr1,expr2):如果两个字符串是一样的则返回0;如果第一个小于第二个则返回-1;否则返回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 |
+-------------------------+-------------------------+

相关学习推荐:mysql视频教程

The above is the detailed content of What are the mysql string functions?. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn