MySQL常用文本处理函数

发表于:2017-11-01

不区分大小写

函数 说明
length() 返回字符串的长度
locate() 找出字符串的一个子串
lower() 将字符串转为小写
upper() 将字符串转为大写
trim() 去掉字符串左右的空格
ltrim() 去掉字符串左边的空格
rtrim() 去掉字符串右边的空格
soundex() 返回字符串的soundex值
substring() 返回子串的字符
left() 返回字符串左边的字符
right() 返回字符串右边的字符

MySQL版本

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.20    |
+-----------+

检索所有数据

mysql> SELECT * FROM articles;
+----+------------+
| id | title      |
+----+------------+
|  1 | hello      |
|  2 | world      |
|  3 | WELCOME    |
|  4 | xiejiahe   |
|  5 | JavaScript |
|  6 | HTML5      |
|  7 | css3       |
+----+------------+

Length函数

返回字符串的长度

mysql> SELECT Length(title) AS title FROM articles;
+-------+
| title |
+-------+
|     5 |
|     5 |
|     7 |
|     8 |
|    10 |
|     5 |
|     4 |
+-------+

Locate()函数

找到返回1,没找到返回0

mysql> SELECT Locate(title, 'xiejiahe') AS title FROM articles;
+-------+
| title |
+-------+
|     0 |
|     0 |
|     0 |
|     1 |
|     0 |
|     0 |
|     0 |
+-------+

Lower函数

将字符串转为小写

mysql> SELECT Lower(title) AS title FROM articles;
+------------+
| title      |
+------------+
| hello      |
| world      |
| welcome    |
| xiejiahe   |
| javascript |
| html5      |
| css3       |
+------------+

Upper()函数

将字符串转为大写

mysql> SELECT Upper(title) AS title FROM articles;
+------------+
| title      |
+------------+
| HELLO      |
| WORLD      |
| WELCOME    |
| XIEJIAHE   |
| JAVASCRIPT |
| HTML5      |
| CSS3       |
+------------+

Trim()函数

去掉左边和右边的空格

mysql> SELECT Trim(title) AS title FROM articles;
+------------+
| title      |
+------------+
| hello      |
| world      |
| WELCOME    |
| xiejiahe   |
| JavaScript |
| HTML5      |
| css3       |
+------------+

Ltrim()函数

去掉左边的空格

mysql> SELECT Ltrim(title) AS title FROM articles;
+------------+
| title      |
+------------+
| hello      |
| world      |
| WELCOME    |
| xiejiahe   |
| JavaScript |
| HTML5      |
| css3       |
+------------+

Rtrim()函数

去掉右边的空格

mysql> SELECT Rtrim(title) AS title FROM articles;
+------------+
| title      |
+------------+
| hello      |
| world      |
| WELCOME    |
| xiejiahe   |
| JavaScript |
| HTML5      |
| css3       |
+------------+

Soundex()函数

这个函数就是用于匹配发音相似的数据, hell和hello发音相似所以匹配成功

mysql> SELECT title FROM articles WHERE SOUNDEX(title)=Soundex('hell');
+-------+
| title |
+-------+
| hello |
+-------+

SubString()函数

截取字符串,-1从倒数第一个开始截取

mysql> SELECT SubString(title, -1) AS title FROM articles;
+-------+
| title |
+-------+
| o     |
| d     |
| E     |
| e     |
| t     |
| 5     |
| 3     |
+-------+

Left()函数

返回了左边3个字符,如果给出的数值超出范围会返回全部

mysql> SELECT Left(title, 3) AS title FROM articles;
+-------+
| title |
+-------+
| hel   |
| wor   |
| WEL   |
| xie   |
| Jav   |
| HTM   |
| css   |
+-------+

Right()函数

返回右边的3个字符,跟Left函数相反

mysql> SELECT Right(title, 3) AS title FROM articles;
+-------+
| title |
+-------+
| llo   |
| rld   |
| OME   |
| ahe   |
| ipt   |
| ML5   |
| ss3   |
+-------+
数据库
广告