字符串函数

最后更新于:2022-04-02 03:56:53

[TOC] ## 字符串函数 ### `length`(慎用,utf8下汉字算三个字节) ### `char_length(s) / character_length(s)` 返回字符换s的字符长度 ### `concat(s1,s2...sn)` 并接字符串 ``` SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS newstring; ``` ### `concat_ws(x, s1,s2...sn)` 带分隔符的并接 ``` SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString; ``` ### `format(x,n)` 将浮点数或整数格式化 `###,###.###` ``` SELECT FORMAT(250500.5634, 2); // 250,500.56 SELECT FORMAT(250500, 3); //250,500.000 ``` ### `insert(s1,x,len,s2)` 替换个字符串 ``` select INSERT("abcdef",1,2,"xy"); //xycdef // 如果替换长度大于替换字符串则返回为空 select INSERT("abcdef",1,4,"xy"); //xyef ``` ### `locate(substr,str)` 子字符串在字符串中的起始位置 ``` select LOCATE("wo","hello word") //7 ``` ### `lcase(s) / lower(s)` 转为小写 ### `ucase(s) / upper(s) ` 转大写 ### `left(s,n) / right(s,n)` 返回前(后)n个字符串 ### `lpad(s1,len,s2) / rpad(s1,len,s2) ` 在开始处填充字符串 ``` select LPAD("abc",5,"x") //xxabc select LPAD("abc",5,"xyz") //xyabc ``` ### `trim(s) / ltrim(s) / rtrim(s)` 去空格 ### `mid(s,n,len) / substring(s,n,len) / substr(s,n,len)` 截取字符串 ### `repeat(s,n)` 对s重复n次 ### `replace(s,s1,s2)` s1 替换为 s2 ``` SELECT REPLACE('abc','ab','x') //xc ``` ### `reverse(s)` 翻转字符串 ### `space(n)` 返回n个空格 ``` select CONCAT("helo",SPACE(2),"word") //helo word ``` ### `strcmp(s1,s2)` 比较字符串是否相等,相等返回0, s1>s2 返回 1 s1 ';