最近做项目遇到这样的需求, 要求实现某小区需要按照小区楼栋单元号房间号进行排序看似很简单的一个需求, 一条 sql 语句搞定, 其实套路很深, 下面小编给大家分享下 Oracle 字符串中包含数字特殊符号的排序问题
问题描述:
某小区, 需要按照小区楼栋单元号房间号进行排序, 但是按照地址描述排序时, 因为字符串中包含数字, 所以造成了如下的结果,
1 号楼之后应该是 2 号楼, 但是查询结果却是 10 号楼
尝试解决
使用正则表达式替换
结果:
虽然楼栋号排序正常了, 但是会发现房间号排序出现了混乱 继续想办法
终极办法:
使用 translate 函数
可以发现, 结果正常显示
以下附上 translate 使用方法
一语法:
TRANSLATE(string,from_str,to_str)
二目的
返回将 (所有出现的)from_str 中的每个字符替换为 to_str 中的相应字符以后的 stringTRANSLATE 是 REPLACE 所提供的功能的一个超集如果 from_str 比 to_str 长, 那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除, 因为它们没有相应的替换字符 to_str 不能为空 Oracle 将空字符串解释为 NULL, 并且如果 TRANSLATE 中的任何参数为 NULL, 那么结果也是 NULL
三允许使用的位置
过程性语句和 SQL 语句
四示例
Sql 代码
- SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual;
- TRANSLATE (
- --------------
- 123456ghij
- SELECT TRANSLATE('abcdefghij','abcdefghij','123456') FROM dual;
- TRANSL
- ----------
- 123456
语法: TR
ANSLATE(expr,from,to)
expr: 代表一串字符, from 与 to 是从左到右一一对应的关系, 如果不能对应, 则视为空值
举例:
- select translate('abcbbaadef','ba','#@') from dual(b 将被#替代, a 将被@替代)
- select translate('abcbbaadef','bad','#@') from dual(b 将被#替代, a 将被@替代, d 对应的值是空值, 将被移走)
因此: 结果依次为:@#c##@@def 和 @#c##@@ef
语法:
TRANSLATE(expr,from,to)
expr: 代表一串字符, from 与 to 是从左到右一一对应的关系, 如果不能对应, 则视为空值
举例:
- select translate('abcbbaadef','ba','#@') from dual(b 将被#替代, a 将被@替代)
- select translate('abcbbaadef','bad','#@') from dual(b 将被#替代, a 将被@替代, d 对应的值是空值, 将被移走)
因此: 结果依次为:@#c##@@def 和 @#c##@@ef
示例如下:
示例一: 将数字转换为 9, 其他的大写字母转换为 X, 然后返回
SELECT TRANSLATE('2KRW229','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"FROM DUAL
示例二: 将数字保留, 将其他的大写字母移除
SELECT TRANSLATE('2KRW229','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789') "Translate example"FROM DUAL
罗勇补充示例如下:
示例三: 示例证明是按照字符来处理, 不是按照字节来处理, 如果 to_string 的字符数比 from_string 多的话, 多出的字符数似乎没有什么用处, 也不会引发异常
- SELECT TRANSLATE('我是中国人, 我爱中国', '中国', 'China') "Translate example"
- FROM DUAL
示例四: 下面的示例证明, 如果 from_string 的字符数大于 to_string, 那么多出的字符会被移除, 也就是 ina 三个字符会从 char 参数中移除, 当然区分大小写啦
- SELECT TRANSLATE('I am Chinese, I love China', 'China', '中国') "Translate example"
- FROM DUAL
示例五: 以下示例证明, 如果第二个参数为空字符串, 整个返回 null
- SELECT TRANSLATE('2KRW229',
- '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
- '')"License"
- FROM DUAL
示例六: 在银行转帐时经常看见账户人只显示姓名的最后一个字, 其余的用星号代替, 我就用 translate 来做个类似的东西吧
- SELECT TRANSLATE('中国人',
- substr('中国人',1,length('中国人') - 1),
- rpad('*',length('中国人'),'*')) "License"
- FROM DUAL
总结
以上所述是小编给大家介绍的解决 Oracle 字符串中包含数字特殊符号的排序问题, 希望对大家有所帮助, 如果大家有任何疑问请给我留言, 小编会及时回复大家的在此也非常感谢大家对 PHPERZ 网站的支持!
来源: http://www.phperz.com/article/18/0318/361478.html