1.length 获取参数值的字节个数
SELECT LENGTH (‘tom’);
SELECT LENGTH (‘张三丰hahaha’);

2.concat 拼接字符串
SELECT CONCAT(lastname,’‘,first_name) AS 姓名 FROM employees;

3.upper、lower
SELECT UPPER(‘tom’);#字符串变大写
SELECT LOWER(‘ToM’);#字符串变小写
#示例：将姓名变大写，名变小写，然后拼接
SELECT
CONCAT(UPPER(lastname),’‘,LOWER(firstname)) AS 姓名
FROM
employees;

#4.substr、substring
#注意SQL中，索引都是从1开始的
#截取从指定索引处后面所有字符
SELECT SUBSTR(‘李莫愁爱上陆展元’,6) out_put;
#截取从指定索引处指定长度的字符
SELECT SUBSTR(‘李莫愁爱上陆展元’,1,3) out_put;
#案例：姓名中首字符大写，其他字符小写然后用拼接，显示出来
SELECT
CONCAT(UPPER(SUBSTR(lastname,1,1)),’‘,LOWER(SUBSTR(last_name,2))) AS 姓名
FROM
employees;

#5.instr 返回子串第一次出现的索引，如果找不到返回0
SELECT
INSTR(‘杨不悔爱上了殷六侠’,’殷六侠’) AS out_put;

6.trim 去除字符串前后指定的字符
SELECT TRIM(‘ 小明 ‘) AS out_put;#默认去除的是空格
SELECT TRIM(‘a’ FROM ‘aaaaaa小a王aaaaaaaaa’) AS out_put;

7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD(‘殷素素’,10,’*’) AS out_put;

8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD(‘殷素素’,10,’%’) AS out_put;

9.replace 替换
SELECT REPLACE(‘张无忌爱上了周芷若’,’周芷若’,’赵敏’) AS out_put;