Detailed introduction to MySQL character functions

#CONCAT() and CONCAT_WS() character connection

mysql> SELECT CONCAT('mysql','5.6');
+-----------------------+
| CONCAT('mysql','5.6') |
+-----------------------+
| mysql5.6              |
+-----------------------+
1 row in set (0.09 sec)

mysql> SELECT CONCAT('mysql','-','5.6');
+---------------------------+
| CONCAT('mysql','-','5.6') |
+---------------------------+
| mysql-5.6                 |
+---------------------------+
1 row in set (0.00 sec)

For example: concatenate the user's first_name and last_name into a string

mysql> SELECT * FROM tdb_test;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | A          | B         |
|  2 | Jack       | Bob       |
|  3 | tom%       | 123       |
+----+------------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT CONCAT(first_name,last_name) AS fullname FROM tdb_test;
+----------+
| fullname |
+----------+
| AB       |
| JackBob  |
| tom%123  |
+----------+
3 rows in set (0.00 sec)

The first of CONCAT_WS() The first parameter is the delimiter, and the following is the character to be connected

mysql> SELECT CONCAT_WS('|','A','B','C');
+----------------------------+
| CONCAT_WS('|','A','B','C') |
+----------------------------+
| A|B|C                      |
+----------------------------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT_WS('-','mysql','5.6');
+------------------------------+
| CONCAT_WS('-','mysql','5.6') |
+------------------------------+
| mysql-5.6                    |
+------------------------------+
1 row in set (0.00 sec)

FORMAT() number formatting

mysql> SELECT FORMAT(12560.75,1);
+--------------------+
| FORMAT(12560.75,1) |
+--------------------+
| 12,560.8           |
+--------------------+
1 row in set (0.01 sec)

LOWER()和 UPPER()
mysql> SELECT LOWER('MySql');
+----------------+
| LOWER('MySql') |
+----------------+
| mysql          |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT UPPER('MySql');
+----------------+
| UPPER('MySql') |
+----------------+
| MYSQL          |
+----------------+
1 row in set (0.00 sec)

LEFT() and RIGHT()

have two parameters respectively. The first one is the string used, and the second one is the number

For example, get the first two digits of MySQL

mysql> SELECT LEFT('MySQL',2);
+-----------------+
| LEFT('MySQL',2) |
+-----------------+
| My              |
+-----------------+
1 row in set (0.00 sec)

For example, get the last two digits of MySQL

mysql> SELECT RIGHT('MySQL',2);
+------------------+
| RIGHT('MySQL',2) |
+------------------+
| QL               |
+------------------+
1 row in set (0.00 sec)

LENGTH Get the string length

mysql> SELECT LENGTH('MySQL');
+-----------------+
| LENGTH('MySQL') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.02 sec)

LTRIM,RTRIM,TRIM

For example, directly use SELECT TRIM( ' mysql ');

will delete the spaces before and after mysql, LTRIM only deletes the spaces on the left, and RTRIM only deletes the spaces on the right.

Other usage: Use TRIM() to delete specific characters on the left and right sides.

For example:

mysql> SELECT TRIM(LEADING '?' FROM '??MySQL???');
+-------------------------------------+
| TRIM(LEADING '?' FROM '??MySQL???') |
+-------------------------------------+
| MySQL???                            |
+-------------------------------------+
1 row in set (0.02 sec)
mysql> SELECT TRIM(TRAILING '?' FROM '??MySQL???');
+--------------------------------------+
| TRIM(TRAILING '?' FROM '??MySQL???') |
+--------------------------------------+
| ??MySQL                              |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TRIM(BOTH '?' FROM '??MySQL???');
+----------------------------------+
| TRIM(BOTH '?' FROM '??MySQL???') |
+----------------------------------+
| MySQL                            |
+----------------------------------+
1 row in set (0.01 sec)

Note: TRIM() can only delete the leading and following characters, but cannot delete the characters in the middle. For example, TRIM('My SQL'); this space cannot be deleted.

REPLACE() string replacement

For example, replace the ? in ??My??SQL??? Replace it with empty

mysql> SELECT REPLACE('??My??SQL???','?','');
+--------------------------------+
| REPLACE('??My??SQL???','?','') |
+--------------------------------+
| MySQL                          |
+--------------------------------+
1 row in set (0.00 sec)

##SUBSTRING() string intercepts the parameters contained in

, intercepts from which number, intercepts which number .

mysql> SELECT SUBSTRING('MySQL',1,2);
+------------------------+
| SUBSTRING('MySQL',1,2) |
+------------------------+
| My                     |
+------------------------+
1 row in set (0.00 sec)

It should be noted that, unlike programming languages, the first bit is 1, not 0.

If you only start from the number and don't intercept a few, it will be intercepted until the end.

mysql> SELECT SUBSTRING('MySQL',3);
+----------------------+
| SUBSTRING('MySQL',3) |
+----------------------+
| SQL                  |
+----------------------+
1 row in set (0.00 sec)

The position can also be a negative value (counting backwards)

mysql> SELECT SUBSTRING('MySQL',-1);
+-----------------------+
| SUBSTRING('MySQL',-1) |
+-----------------------+
| L                     |
+-----------------------+
1 row in set (0.04 sec)

Note, it can only be the beginning The position can have negative values, but the interception length cannot have negative values.

LIKE pattern matching (commonly used in queries)

mysql> SELECT 'MySQL' LIKE 'M%';
+-------------------+
| 'MySQL' LIKE 'M%' |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

% refers to any one or more digits

mysql> SELECT * FROM tdb_test;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | A          | B         |
|  2 | Jack       | Bob       |
|  3 | tom%       | 123       |
+----+------------+-----------+
3 rows in set (0.00 sec)

For example, query, users whose names contain o

mysql> SELECT * FROM tdb_test WHERE first_name LIKE '%o%';
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  3 | tom%       | 123       |
+----+------------+-----------+
1 row in set (0.00 sec)

For example, query, users whose names contain %

mysql> SELECT * FROM tdb_test WHERE first_name LIKE '%%%';
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | A          | B         |
|  2 | Jack       | Bob       |
|  3 | tom%       | 123       |
+----+------------+-----------+
3 rows in set (0.00 sec)

发现显示的是所有查询结果。因为mysql会认为上述的%都为通配符。正确操作如下

mysql> SELECT * FROM tdb_test WHERE first_name LIKE '%1%%' ESCAPE '1';
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  3 | tom%       | 123       |
+----+------------+-----------+
1 row in set (0.00 sec)

注：%：任意个字符.

_:任意一个字符。

 以上就是MySQL字符函数的详细介绍的内容，更多相关内容请关注PHP中文网（www.php.cn）！