10.MySQL字符函数

字符函数

连接字符：CONCAT()

mysql> SELECT CONCAT('www.','toly.','top'); +------------------------------+ | CONCAT('www.','toly.','top') | +------------------------------+ | www.toly.top | +------------------------------+ 1 row in set (0.00 sec)

创建名单表并插入数据：

mysql> CREATE TABLE 名单 ( -> 姓 VARCHAR(12) NOT NULL, -> 名 VARCHAR(24) NOT NULL -> ); Query OK, 0 rows affected (0.07 sec) mysql> INSERT 名单 VALUES('张','飞'),('刘','备'),('关','羽'); Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT *FROM 名单; +-----+-----+ | 姓 | 名 | +-----+-----+ | 张 | 飞 | | 刘 | 备 | | 关 | 羽 | +-----+-----+ 3 rows in set (0.00 sec)

全名拼接输出：

mysql> SELECT CONCAT(姓,名) AS 三英 FROM 名单; +--------+ | 三英 | +--------+ | 张飞 | | 刘备 | | 关羽 | +--------+ 3 rows in set (0.00 sec)

指定分隔符连接字符：CONCAT_WS()

mysql> SELECT CONCAT_WS('.','www','toly','top'); +-----------------------------------+ | CONCAT_WS('.','www','toly','top') | +-----------------------------------+ | www.toly.top | +-----------------------------------+ 1 row in set (0.00 sec)

格式化数字：FORMAT()

mysql> SELECT FORMAT(1299402.779040,2); +--------------------------+ | FORMAT(1299402.779040,2) | +--------------------------+ | 1,299,402.78 | +--------------------------+ 1 row in set (0.02 sec)

变小写：LOWER()

mysql> SELECT LOWER('ToLy'); +---------------+ | LOWER('ToLy') | +---------------+ | toly | +---------------+ 1 row in set (0.00 sec)

变大写：UPPER()

mysql> SELECT UPPER('ToLy'); +---------------+ | UPPER('ToLy') | +---------------+ | TOLY | +---------------+ 1 row in set (0.00 sec)

从左侧取字符：LEFT()

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

从左侧取字符：LEFT()

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

字符长度(注:含空格)：LENGTH()

mysql> SELECT LENGTH('To Ly'); +-----------------+ | LENGTH('To Ly') | +-----------------+ | 5 | +-----------------+ 1 row in set (0.00 sec)

删开头左空格:LTRIM()

mysql> SELECT LTRIM(' To Ly '); +--------------------+ | LTRIM(' To Ly ') | +--------------------+ | To Ly | +--------------------+ 1 row in set (0.00 sec)

删结尾右空格:LTRIM()

mysql> SELECT RTRIM(' To Ly '); +--------------------+ | RTRIM(' To Ly ') | +--------------------+ | To Ly | +--------------------+ 1 row in set (0.00 sec)

删开头左空格和结尾右空格:TRIM()

mysql> SELECT TRIM(' To Ly '); +-------------------+ | TRIM(' To Ly ') | +-------------------+ | To Ly | +-------------------+ 1 row in set (0.00 sec)

替换：REPLACE()

mysql> SELECT REPLACE(' To Ly '," ",""); +-----------------------------+ | REPLACE(' To Ly '," ","") | +-----------------------------+ | ToLy | +-----------------------------+ 1 row in set (0.00 sec)

截取:SUBSTRING(“字符串”,第几个开始(左正右负),截取几个)

mysql> SELECT SUBSTRING("ToLy",2,1); +-----------------------+ | SUBSTRING("ToLy",2,1) | +-----------------------+ | o | +-----------------------+ 1 row in set (0.00 sec) mysql> SELECT SUBSTRING("ToLy",-2,2); +------------------------+ | SUBSTRING("ToLy",-2,2) | +------------------------+ | Ly | +------------------------+ 1 row in set (0.00 sec)

匹配字符：LIKE()(%:任意字符 _:任一字符)

mysql> INSERT 名单 VALUES('诸葛','亮'); Query OK, 1 row affected (0.00 sec) mysql> SELECT *FROM 名单; +--------+-----+ | 姓 | 名 | +--------+-----+ | 张 | 飞 | | 刘 | 备 | | 关 | 羽 | | 诸葛 | 亮 | +--------+-----+ 4 rows in set (0.00 sec) mysql> SELECT*FROM 名单 WHERE 姓 LIKE('%葛%') ; +--------+-----+ | 姓 | 名 | +--------+-----+ | 诸葛 | 亮 | +--------+-----+ 1 row in set (0.00 sec)

如果已经有’%’:(其中0可任意指定，与后统一即可)

mysql> INSERT 名单 VALUES('赵%','云'); Query OK, 1 row affected (0.03 sec) mysql> SELECT*FROM 名单 WHERE 姓 LIKE('%0%%') ESCAPE '0'; +------+-----+ | 姓 | 名 | +------+-----+ | 赵% | 云 | +------+-----+ 1 row in set (0.00 sec)

转载于:https://www.cnblogs.com/toly-top/p/9782016.html