substr(oracle) vs substring(mssql) Out了吧?
本文讨论的是MSSQL和ORACLE的字符截取函数,substring和substr,的一点差异。下面是验证经过:
Oracle:
select substr('123456',0,3) result from dual
union all
select substr('123456',1,3) result from dual
union all
select substr('123456',2,3) result from dual
union all
select substr('123456',3,3) result from dual
union all
select substr('123456',-3,3) result from dual
结果:
RESULT
123
123
234
345
456
MSSQL:
select substring('123456',0,3) as result
union all
select substring('123456',1,3) as result
union all
select substring('123456',2,3) as result
union all
select substring('123456',3,3) as result
union all
select substring('123456',-3,3) as result
结果集:
result
12
123
234
345
(空)
结论:起始位置都是从1开始,但是oracle有容错,如果从0开始返回结果是从1开始的。oracle更强可以支持倒数截取字符。
ps:为啥研究这个,因为同事抱怨oracle的substr不是从1开始,跟MSSQL的不同,看吧结论出来了。
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
"作者:" 数据酷软件工作室
"出处:" http://datacool.cnblogs.com
"专注于CMS(综合赋码系统),MES,WCS(智能仓储设备控制系统),WMS,商超,桑拿、餐饮、客房、足浴等行业收银系统的开发,15年+从业经验。因为专业,所以出色。"
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
"作者:" 数据酷软件工作室
"出处:" http://datacool.cnblogs.com
"专注于CMS(综合赋码系统),MES,WCS(智能仓储设备控制系统),WMS,商超,桑拿、餐饮、客房、足浴等行业收银系统的开发,15年+从业经验。因为专业,所以出色。"
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++