17,081
社区成员
发帖
与我相关
我的任务
分享
--1.创建测试表
select ascii('安') a,'安' x from dual union all
select ascii('被') a,'被' x from dual union all
select ascii('走') a,'走' x from dual union all
select ascii('神') a,'神' x from dual union all
select ascii('哎') a,'哎' x from dual union all
select ascii('澳') a,'澳' x from dual union all
select ascii('阿') a,'阿' x from dual
order by x
--2.应该根据ascii排序的。
SQL> select ascii('安') a,'安' x from dual union all
2 select ascii('被') a,'被' x from dual union all
3 select ascii('走') a,'走' x from dual union all
4 select ascii('神') a,'神' x from dual union all
5 select ascii('哎') a,'哎' x from dual union all
6 select ascii('澳') a,'澳' x from dual union all
7 select ascii('阿') a,'阿' x from dual
8 order by x;
A X
---------- --------------------------------
45218 阿
45221 哎
45234 安
45252 澳
45499 被
51697 神
55263 走
7 rows selected
--3.分析:"哎"和"安"对于得ascii为45221和45234
故要查询出"安"和"哎"只要ascii的范围包括45221和45234即可.
例:
SQL> select chr(45219) from dual;
CHR(45219)
----------
埃
SQL> select chr(45235) from dual;
CHR(45235)
----------
俺
--写法1:
SQL> select * from tt where substr(name,1,1) between '挨' and '俺';
NAME
----
安
哎
--写法2:
SQL> select * from tt where substr(name,1,1) between chr(45221) and chr(45234);
NAME
----
安
哎
--写法3:
SQL> select * from tt where substr(name,1,1) between '阿' and '澳';
NAME
----
安