|
#3. 字符串函数
格式：ASCII('Abcdfsf')解说：求字符串中第一个字符的ASCII码
格式：CHAR_LENGTH(str)解说:求字符串str长度CHAR_LENGTH
格式：LENGTH(str)
解说：字符串所占字节数SELECT ASCII('Abcdfsf'),CHAR_LENGTH('hello'),CHAR_LENGTH('我们'),
LENGTH('hello'),LENGTH('我们')
FROM DUAL;
/*
+------------------+----------------------+-----------------------+-----------------+------------------+
| ASCII('Abcdfsf') | CHAR_LENGTH('hello') | CHAR_LENGTH('我们') | LENGTH('hello') | LENGTH('我们') |
+------------------+----------------------+-----------------------+-----------------+------------------+
| 65 | 5 | 2 | 5 | 6 |
+------------------+----------------------+-----------------------+-----------------+------------------+
*/
格式：CONCATCONCAT(s1,s2…n)解说：将所有的字符窜拼接成一字符串
# xxx worked for yyy
SELECT CONCAT(emp.last_name,' worked for ',mgr.last_name) "details"
FROM employees emp JOIN employees mgr
WHERE emp.`manager_id` = mgr.employee_id;
/*部分输出
+--------------------------------+
| details |
+--------------------------------+
| Kochhar worked for King |
| De Haan worked for King |
| Hunold worked for De Haan |
*/
格式：CONCAT_WS(x,s1,s2,.sn)解说：用第一个参数分隔连接后面的字符串
SELECT CONCAT_WS('-','hello','world','hello','beijing')
FROM DUAL;
/*
+--------------------------------------------------+
| CONCAT_WS('-','hello','world','hello','beijing') |
+--------------------------------------------------+
| hello-world-hello-beijing |
+--------------------------------------------------+
*/
格式：INSERT(str, idx, len,replacestr)
解说：将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr，字符串的索引是从1开始的！(Java从0开始的)
SELECT INSERT('helloworld',2,3,'aaaaa'),REPLACE('hello','lol','mmm'),REPLACE('hello','lo','mmm')
FROM DUAL;
/*
+----------------------------------+------------------------------+-----------------------------+
| INSERT('helloworld',2,3,'aaaaa') | REPLACE('hello','lol','mmm') | REPLACE('hello','lo','mmm') |
+----------------------------------+------------------------------+-----------------------------+
| haaaaaoworld | hello | helmmm |
+----------------------------------+------------------------------+-----------------------------+
*/格式：UPPER(str)解说：将字符窜str全部转成大写
#大小写转换
SELECT UPPER('HelLo'),LOWER('HelLo')
FROM DUAL;
/*
+----------------+----------------+
| UPPER('HelLo') | LOWER('HelLo') |
+----------------+----------------+
| HELLO | hello |
+----------------+----------------+
*/格式：LOWER(str)解说：将字符全部转换成小写的字符窜。
SELECT last_name,salary
FROM employees
WHERE LOWER(last_name) = 'King';
/*严格说应该查不到-->但是Mysql大小写不严格
+-----------+----------+
| last_name | salary |
+-----------+----------+
| King | 24000.00 |
| King | 10000.00 |
+-----------+----------+
*/ 格式：LEFT(str,n)解说：从str字符窜的左边开始截取 指定宽度n的字符窜，索引从1开始
SELECT LEFT('hello',2),RIGHT('hello',3),RIGHT('hello',13)
FROM DUAL;
/*
+-----------------+------------------+-------------------+
| LEFT('hello',2) | RIGHT('hello',3) | RIGHT('hello',13) |
+-----------------+------------------+-------------------+
| he | llo | hello |
+-----------------+------------------+-------------------+
*/格式：LPAD(salary,10,'$') 解说；左填充，宽度10，不足在左边补充$字符
# LPAD:左填充
# RPAD:右填充，
SELECT employee_id,last_name,LPAD(salary,10,'$'),LPAD(salary,10,' ')
FROM employees;
/*
+-------------+-------------+---------------------+---------------------+
| employee_id | last_name | LPAD(salary,10,'$') | LPAD(salary,10,' ') |
+-------------+-------------+---------------------+---------------------+
| 100 | King | $$24000.00 | 24000.00 |
| 101 | Kochhar | $$17000.00 | 17000.00 |
| 102 | De Haan | $$17000.00 | 17000.00 |
| 103 | Hunold | $$$9000.00 | 9000.00 |
*/
#TRIM去掉首尾空格
#LTRIM去掉左侧空格
#TRIM('oo' FROM 'ooheolloo')去掉'oo'
SELECT CONCAT('---',LTRIM(' h el lo '),'***'),
TRIM('oo' FROM 'ooheolloo')
FROM DUAL;
/*
+-----------------------------------------------+-----------------------------+
| CONCAT('---',LTRIM(' h el lo '),'***') | TRIM('oo' FROM 'ooheolloo') |
+-----------------------------------------------+-----------------------------+
| ---h el lo *** | heoll |
+-----------------------------------------------+-----------------------------+
*/
#REPEAT(str,n):重复n次str
#SPACE(n):提供n个空格
#STRCMP：比较字符串大小
SELECT REPEAT('hello',4),LENGTH(SPACE(5)),STRCMP('abc','abe')
FROM DUAL;
/*
+----------------------+------------------+---------------------+
| REPEAT('hello',4) | LENGTH(SPACE(5)) | STRCMP('abc','abe') |
+----------------------+------------------+---------------------+
| hellohellohellohello | 5 | -1 |
+----------------------+------------------+---------------------+
*/
#SUBSTR(str,i,len):截取str中i处起len个字符
#LOCATE('ll','hello')定位‘ll’首次出现的位置，未找到返回0
SELECT SUBSTR('hello',2,2),LOCATE('ll','hello'),LOCATE('lll','hello')
FROM DUAL;
/*
+---------------------+----------------------+-----------------------+
| SUBSTR('hello',2,2) | LOCATE('ll','hello') | LOCATE('lll','hello') |
+---------------------+----------------------+-----------------------+
| el | 3 | 0 |
+---------------------+----------------------+-----------------------+
*/
#ELT:返回指定位置的字符串
#FIELD(s,s1,...)：返回s在字符列表中首次出现的位置
#FIND_IN_SET(s1,s2):返回s1在s2中首次出现的位置
SELECT ELT(2,'a','b','c','d'),FIELD('mm','gg','jj','mm','dd','mm'),
FIND_IN_SET('mm','gg,mm,jj,dd,mm,gg')
FROM DUAL;
/*
+------------------------+--------------------------------------+---------------------------------------+
| ELT(2,'a','b','c','d') | FIELD('mm','gg','jj','mm','dd','mm') | FIND_IN_SET('mm','gg,mm,jj,dd,mm,gg') |
+------------------------+--------------------------------------+---------------------------------------+
| b | 3 | 2 |
+------------------------+--------------------------------------+---------------------------------------+
*/
#NULLIF(s1,s2):字符串s1和s2相等返回NULL,不相等返回s1
SELECT employee_id,NULLIF(LENGTH(first_name),LENGTH(last_name)) "compare"
FROM employees;
/*姓和名一样长返回NULL
+-------------+---------+
| employee_id | compare |
+-------------+---------+
| 100 | 6 |
| 101 | 5 |
| 102 | 3 |
| 103 | 9 |
| 104 | NULL |
*/
|