SQL截取字符串替换

首先申明，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	停用