MYSQL笔记_字符串函数

    
1，ASCII(S)只读取第一个字符

        SELECT ASCII(`ASDFGHJKL`)
        FROM DUAL;        

---

2，CHAR_LENGTH(S) 统计（``）里有几个字符    
    LENGTH(S)统计（``）里有几个字节 

        SELECT CHAR_LENGTH(`hello`),CHAR_LENGTH(`我们`),LENGTH(`hello`)LENGTH(`我们`)
        FROM DUAL;           

---

3，将多个字符串拼接到一起

        CONCAT(S1,S2...Sn) 将字符连接 

        SELECT CONCAT(em.last_name,`worked for`,mgr.last_name) AS `details`
        FROM employees em JOIN employees mgr
        ON em.employee_id = mgr.manager_id
        输出结果：TOM workde for JERRY

        CONCAT(X,S1,S2...Sn) 将字符用x连接  

        CONCAT_WS(X,S1,S2...Sn)    将字符用x连接            
        SELECT CONCAT_WS(`-`,`hell`，`world`,`hello`,`beijing`)
        FROM DUAL;
        输出结果：hell-world-hello-beijing

4，替换

        INSERT(str,idx,len,replacestr) (目标字符，起点，选择x个，变为`y`)  

        SELECT INSERT(`helloworld`,2,3,`aaa`)
        FROM DUAL;
        输出结果：haaaoworld ——> ell被替换

           
        REPLACE(str,a,b) 用b替换a  

        SELECT REPLACE(`helloworld`,`ll`,`mm`)
        FROM DUAL;
        输出结果：hemmoworld
        ll被替换l没有被替换

---

5，大小写

        UPPER(S) , UCASE(S)变大写 
        LOWER(S) , LCASE(S)变小写

        SELECT UPPER(`hello`),UCASE(`hello`) , LOWER(`HELLO`),LCASE(`HELLO`)
        FROM DUAL;   

---

 
6，截取

        LEFT(str,n)
        RIGHT(str,n)(目标，从左开始截取的数量)

        SELECT LEFT(`hello`,2),RIGHT(`hello`,2)
        FROM DUAL;
        输出结果:`he` `lo`     

---

   
7，填充

        LPAD(str,len,pad)从左边开始用pad填充目标字符串达到len长度
        RPAD(str,len,pad)从右边开始用pad填充目标字符串达到len长度

        SELECT LPAD(`hello`,8,`-`),RPAD(`hello`,8,`-`)
        FROM DUAL;
        输出结果:`---hello` `helllo---`

---

8，去空格

        LTRIM(S)去除左空格
        RTRIM(S)去除右空格
        TRIM(S)去除开始与结尾的空格

        SELECT LTRIM(`   HE  LL  O  `),RTRIM(`   HE  LL  O  `),TRIM(`   HE  LL  O  `)
        FROM DUAL;    

---

9，去字符

        TRIM(S1 FROM S)去掉S开头和结尾的S1
        TRIM(LEADING S1 FROM S)去掉S头部的的S1
        TRIM(TRAILING S1 FROM S)去掉S尾部的的S1

        SELECT TRIM(`OO` FROM `OOHEOLLOO`)，TRIM(LEADING `OO` FROM `OOHEOLLOO`)，TRIM(TRAILING `OO` FROM `OOHEOLLOO`)
        FROM DUAL;
        输出结果：'HEOLL' 'HEOLLOO' 'OOHEOLL'      

---

10，重复

        REPEAT(str,n)重复n次

        SELECT REPEAT(`HELLO`,4)
        FROM DUAL;
        输出结果：'HELLOHELLOHELLOHELLO'    

---

11，空格  

        SPACE(n)n个空格

        SELECT SPACE(5)
        FROM DUAL;    

---

12，比大小

        STRCMP(S1,S2)左边大是1,右边大是-1

        SELECT STRCMP(`abc`，`abd`)
        FROM DUAL;
        输出结果：-1    

---

13，取字符

        SUBSTR(s,index,len)

        从s里，index开头，取len个字符

        SELECT SUBSTR(`HELLO`,2,1)
        FROM DUAL;
        输出结果：'E'

            
        ELT(M,S1,S2...Sn)

        取第Sm个字符    

        SELECT ELT(3,`ABC`,`DEF`,`GHI`,`JKL`)
        FROM DUAL;
        输出结果：'GHI'            

---

14，定位

        LOCATE(substr,str)

        从substr里定位str在substr里首次出现的位置

        SELECT LOCATE(`L`,`HELLO`)
        FROM DUAL;
        输出结果：3             

---

         FIELD(S,S1...Sn)  

        定位S在S1,S2...Sn里里一次出现的位置 

        SELECT FIELD(`AA`,`BB`,`CC`,`AA`,`BB`,`CC`)
        FROM DUAL;
        输出结果：3           

---

         
         FIND_IN_SET(S1,S2)

        定位S在S2里里一次出现的位置 

        SELECT FIND_IN_SET(`AA`,`BB,CC,DD,AA,BB`)
        FROM DUAL;
        输出结果：4    

---

15，反转

        REVERSE(S)

        SELECT REVERSE(`ABC`)
        FROM DUAL;
        输出结果：'CBA'            

---

16，查找相同NULL  

        NULLIF(value1，value2)

        SELECT NULLIF(1,1),NULLIF(3,2)
        FROM DUAL;
        输出结果：'NULL' '3'