用sql久了, 自然就越发觉得sql的方便, 于是研究研究sql的内置函数
1. CONCAT(S1, S2, Sn) 作用:连接S1,S2...为一个字符串
例子:
SELECT CONCAT(ProjectName, ProjectOrgan) AS uname FROM meta_project LIMIT 1;
2. INSERT (str, x, y, instr)作用: 从某位置开始y个字符长度的替换
SELECT INSERT(ProjectName, 3, 1, 'aa') AS uname FROM meta_project LIMIT 1;
3. LOWER(str) 作用: 将字符串str中所有字符变为小写
SELECT
LOWER(ProjectName) AS uname
FROM
meta_project;
4. UPPER(str)作用: 将字符串str中所有字符变为大写
SELECT UPPER("abcd") AS uname FROM meta_project;
5. LEFT(str, x) 作用: 返回字符串str最左边的x个字符
SELECT LEFT("abcd", 3) AS uname FROM meta_project;
6. RIGHT(str,x) 作用: 返回字符串str最右边的x个字符
SELECT UPPER(RIGHT("abcd", 3)) AS uname FROM meta_project;
7. LPAD(str, n, pad)用字符串pad对str最左边进行填充,直到长度为n个字符长
SELECT LPAD("abcd", 8, '1') AS uname FROM meta_project;
8. RPAD(str, pad)用字符串pad对str最右边进行填充,直到长度为n个字符长
SELECT RPAD("abcd", 8, '1') AS uname FROM meta_project;
9. LTRIM(str) 去掉字符串左侧的空格
SELECT LTRIM(" abcde") AS uname FROM meta_project;
10 . RTRIM(str) 作用: 去掉字符串右侧的空格
SELECT RTRIM(" abcde ") AS uname FROM meta_project;
11. TRIM(str) 作用: 去掉目标字符串的开头和结尾的空格
SELECT TRIM(" abcde ") AS uname FROM meta_project;
12. REPEAT(str, n)作用:str重复n次
SELECT REPEAT("abcd", 3) AS uname FROM meta_project limit 1;
13. REPLACE(str, a, b)作用: 替换
SELECT REPLACE("abcd", 'a', 'd') AS uname FROM meta_project limit 1;
14. STRCMP(s1, s2)作用:比较字符串s1和s2的ascii码值的大小
SELECT STRCMP('a','b'),STRCMP('b','b'),STRCMP('c','b') FROM meta_project limit 1;
15. SUBSTRING(str, x, y)作用: 提取索引x到y的的字符
SELECT SUBSTRING("abcdefghigkl" FROM 1 FOR 5) FROM meta_project limit 1;