字符函数
length
concat
substr
instr
trim
upper
lower
lpad
rpad
replace
1.length:获取参数值的字节个数
SELECT LENGTH('mysql');#5
SELECT LENGTH('我的sql');#9 utf8一个汉字3字节 GBK2字节
2.concat:拼接字符串
SELECT CONCAT(last_name,' ',first_name) 姓名
FROM employees;
3.upper、lower
SELECT UPPER('java');#JAVA
SELECT LOWER("jAVA");#java
#示例:将姓大写,名变小写,然后拼接
SELECT CONCAT(UPPER(last_name),' ',LOWER(first_name)) 姓名
FROM employees;
4.substr\substring 截取索引后面所有字符
注意:索引是1开始
SELECT SUBSTR('今天是晴天',3); #是晴天
SELECT SUBSTR('今天是晴天',1,2);#今天
#案例:姓名中首字符大写,其他字符小写,然后用_拼接显示
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),
LOWER(SUBSTR(last_name,2)),
'_',
LOWER(first_name)) 姓名
FROM employees;
5.instr 返回子串在大串中的第一次出现起始索引
SELECT INSTR('helloworldworld','world') AS output;#6
6.trim 去前后空格
SELECT LENGTH(TRIM(' hello ')) AS output;#5
SELECT TRIM('a' FROM 'aaaaaaa小aaaaa明aaaaaaaaaaaaa') AS output;
#小aaaaa明
7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('小明',10,'s') AS output;
8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('小红',10,'s') AS output;
9.replace 替换
SELECT REPLACE('哈哈哈哈哈哈嗝','哈','h') AS output;