mysql基础复习——字符函数

表结构

1、CONCAT( )函数

作用：连接字符串

SELECT CONCAT(stu_name,'_',stu_age) AS '姓名，年龄' FROM student

2、LENGTH( “STRING” )

作用：返回字符串长度

SELECT stu_name AS "姓名", LENGTH(stu_name) AS "姓名长度" FROM student

3、UPPER()和LOWER()

作用：UPPER：英文大写，LOWER：英文小写

SELECT UPPER(stu_name) AS "大写", LOWER(stu_name) AS "小写" FROM student
# 数据有限，暂且将就

4、SUBSTR（）

作用：截取字符串的子串
该函数一共有三个参数
SUBSTR( ‘string’, INDEX , LENGTH)； string为需要截取的字符串，INDEX为索引值，LENGTH为截取子串的长度，若不规定LENGTH的值则子串长度为从索引开始一直到字符串结尾

# 在一个字符串中从索引3开始截取一个长度为4的子串
# 字符串（随便敲的）：铝型材过热欧拉河南南路
SELECT "铝型材过热欧拉河南南路" AS "字符串", SUBSTR("铝型材过热欧拉河南南路", 3, 4) AS "子串"

PS：mysql中，所有索引值都是从1开始

# 小案例：将一个字符串的前四位改为小写，后四位改为大写并拼接在一起
SELECT CONCAT(LOWER(SUBSTR("ABCDefgh",1,4)), "___", UPPER(SUBSTR("ABCDefgh",5,4))) AS 'substring'

5、INSTR()

INSTR( str1, str2)
作用：在str1中查找str2，并返回str2在str1中的索引值，若找不到则返回0
PS：MySQL索引值从1开始，若str1中存在str2，不可能会返回0

SELECT INSTR("劳资天下第一", "天下") AS "索引值"

6、TRIM、LTRIM、RTRIM

TRIM( “string” )函数
默认去除字符串前后两侧的空格

SELECT TRIM("   哈  哈     哈   ") AS "TRIM"

TRIM ( [位置] [要移除的字串] FROM ] 字串)
位置：LEADING (起头), TRAILING (结尾), or BOTH (起头及结尾)
若不填写 【要移除的字串】，则默认移除空格

SELECT TRIM(LEADING "X" FROM "XXXXXXXXXXXX撒地方为人父微软XXXXXXXXXXX") AS "TRIM"
SELECT TRIM(TRAILING "X" FROM "XXXXXXXXXXXX撒地方为人父微软XXXXXXXXXXX") AS "TRIM"
SELECT TRIM("X" FROM "XXXXXXXXXXXX撒地方为人父微软XXXXXXXXXXX") AS "TRIM"

LTRIM( string ), RTRIM( string )
分别是去除左侧空格和去除右侧空格，且仅能去除空格

7、LPAD、RPAD

LPAD( “字符串”, 字符串总长度, “要填充的字串” ) # 向左填充
RPAD( “字符串”, 字符串总长度, “要填充的字串” ) # 向右填充
字符串总长度：填充后字符串的总长度，若设定长度小于或等于原字符串长度，则不会填充

SELECT LPAD("HELLOWORLD",20,"*") AS "LPAD"

SELECT RPAD("HELLOWORLD",20,"*") AS "RPAD"

8、REPLACE

REPLACE( “字符串”, “被替换”, “替换” )

select replace("world, hello world", "world", "honey") as "REPLACE"