SQL截取字符串替换
- 字符串截取
- 1. left()截取左边
- 2. right()截取右边
- 3. SUBSTR() / SUBSTRING() 截取
- SUBSTRING(name,5,3) 截取name这个字段 从第五个字符开始 只截取之后的3个字符:
- SUBSTRING(name,3) 截取name这个字段 从第三个字符开始,之后的所有个字符:
- SUBSTRING(name, -4) 截取name这个字段的第 4 个字符位置(倒数)开始取,直到结束:
- SUBSTRING(name, -4,2) 截取name这个字段的第 4 个字符位置(倒数)开始取,只截取之后的2个字符:
- substring_index('www.baidu.com', '.', 2) 截取第二个 '.' 之前的所有字符
- substring_index('www.baidu.com', '.', -2) 截取第二个 '.' (倒数)之后的所有字符:
- SUBSTR(name, 1, CHAR_LENGTH(name)-3) 截取name字段,取除name字段后三位的所有字符:
- 字符串替换
- 1. 字段为null的时候替换为0
- 2. 字段中包含qq替换为163
- 3. 字段某值替换为某值
- 4. 有值则使用, 无值则替换
- 5. 条件true则用value1, 否则用value2
首先申明,substr()是基于Oracle的,substring()是基于SQL Server的,切记不可混用,否则会报错!
MySQL: SUBSTR( ), SUBSTRING( )
Oracle: SUBSTR( )
SQL Server: SUBSTRING( )
语法区别 | MySQL | Oracle |
/ | truncate(123.123,2) | TRUNC(123.123,2) |
空值返0 | IFNULL、COALESCE | nvl、nvl2 |
时间转换为字符串型时间 | date_format(NOW(),’%Y-%m-%d’) | to_char(sysdate, ‘YYYY-MM-DD’) |
字符串型时间转换为时间类型 | date_format(NOW(),’%Y-%m-%d’) | date_format(NOW(),’%Y-%m-%d’) |
获取字符串长度 | SELECT char_length(str) | SELECT length(str) |
整形转换字符串 | CAST(123 AS CHAR(3)) | to_char(123) |
字符串转整型 | cast(‘123’ as SIGNED) | to_number(‘123’) |
连表 | select * from ta left join tb on ta.id=tb.id; #左关联 select * from ta right join tb on ta.id=tb.id; #右关联 | select * from ta, tb where ta.id = tb.id(+); #左关联 select * from ta, tb where ta.id(+) = tb.id; #右关联 |
排序 | select * from USER A order by IF(ISNULL(A.REMARK),1,0),A.REMARK desc ; select * from USER A order by IF(ISNULL(A.REMARK),0,1),A.REMARK desc | SELECT * FROM USER A ORDER BY A.REMARK DESC NULLS FIRST; SELECT * FROM USER A ORDER BY A.REMARK DESC NULLS LAST |
字符串截取
1. left()截取左边
left(name,4)截取左边的4个字符 :
SELECT LEFT(201809,4) 年
结果:2018
2. right()截取右边
right(name,2)截取右边的2个字符:
SELECT RIGHT(201809,2) 月
结果:09
3. SUBSTR() / SUBSTRING() 截取
SUBSTRING(name,5,3) 截取name这个字段 从第五个字符开始 只截取之后的3个字符:
SELECT SUBSTRING('一二三四五六七',5,3)
结果:五六七
SUBSTRING(name,3) 截取name这个字段 从第三个字符开始,之后的所有个字符:
SELECT SUBSTRING('一二三四五六七',3)
结果:三四五六七
SUBSTRING(name, -4) 截取name这个字段的第 4 个字符位置(倒数)开始取,直到结束:
SELECT SUBSTRING('一二三四五六七',-4)
结果:四五六七
SUBSTRING(name, -4,2) 截取name这个字段的第 4 个字符位置(倒数)开始取,只截取之后的2个字符:
SELECT SUBSTRING('一二三四五六七',-4,2)
结果:三四五六七
注意:我们注意到在函数 substring(str,pos, len)中, pos 可以是负值,但 len 不能取负值。
substring_index(‘www.baidu.com’, ‘.’, 2) 截取第二个 ‘.’ 之前的所有字符
SELECT substring_index('www.baidu.com', '.', 2)
结果:www.baidu
substring_index(‘www.baidu.com’, ‘.’, -2) 截取第二个 ‘.’ (倒数)之后的所有字符:
SELECT substring_index('www.baidu.com', '.', -2)
结果:baidu.com
SUBSTR(name, 1, CHAR_LENGTH(name)-3) 截取name字段,取除name字段后三位的所有字符:
SELECT SUBSTR('一二三四五六七', 1, CHAR_LENGTH('一二三四五六七')-3)
结果:一二三四
字符串替换
1. 字段为null的时候替换为0
SELECT coalesce(exp1,exp2,…) as info FROM table1
SELECT id,pass_word,COALESCE(pass_word,0) FROM sys_user
id | pass_word | COALESCE(pass_word,0) |
1 | admin123456 | admin123456 |
2 | null | 0 |
2. 字段中包含qq替换为163
SELECT id,email,REPLACE(email,'qq','163') FROM sys_user
id | email | REPLACE(email,‘qq’,‘163’) |
1 | 8050627@qq.com | 8050627@163.com |
2 | 15502266662@126.com | 15502266662@126.com |
3. 字段某值替换为某值
Oracle关键字为 DECODE
SELECT
id,
status,
CASE
status
WHEN 0 THEN
'停用'
WHEN 1 THEN
'启用' ELSE '未知'
END AS statusName
FROM
sys_user
id | status | statusName |
1 | 0 | 停用 |
2 | 1 | 启用 |
3 | 2 | 未知 |
4. 有值则使用, 无值则替换
Oracle关键字为 nvl
select id,status, IFNULL(status,'无状态') from sys_user
id | status | IFNULL(status,‘无状态’) |
1 | 0 | 停用 |
2 | 1 | 启用 |
3 | null | 无状态 |
5. 条件true则用value1, 否则用value2
Oracle关键字为 nvl2
select id,status, IF(status=1,'启用','停用') from sys_user
id | status | IF(status=1,‘启用’,‘停用’) |
1 | 0 | 停用 |
2 | 1 | 启用 |
3 | 2 | 停用 |