虽然oracle在10g以后支持了正则表达式,10g之前的版本可以巧妙利用oracle的内置acsi()i函数来实现字符串匹配的功能。
一、chr()函数将ASCII码转换为字符
SQL> SELECT chr(48),chr(57),chr(65),chr(90),chr(97),chr(122) FROM dual;
CHR(48) CHR(57) CHR(65) CHR(90) CHR(97) CHR(122)
------- ------- ------- ------- ------- --------
0 9 A Z a z
二、ascii()函数将字符转换为ASCII码
SQL> SELECT ascii('0') ,ascii('9'), ascii('A'),ascii('Z'),ascii('a') ,ascii('z') FROM DUAL;
ASCII('0') ASCII('9') ASCII('A') ASCII('Z') ASCII('A') ASCII('Z')
---------- ---------- ---------- ---------- ---------- ----------
48 57 65 90 97 122
如果我们想查找某个字段中第一个字母必的范围是‘A-Z’,第二个字母的范围是‘0-9’,看看如何用ascii函数来实现。
SQL> SELECT * FROM TEST ;
DEPTNO DNAME LOC
------ -------------- -------------
10 A1_BJ BEIJING
20 B2_SH SHANGHAI
30 CSALES CHINA
40 D3 CHENGDU
SQL> SELECT *
2 FROM TEST
3 WHERE ASCII(UPPER(SUBSTR(DNAME, 1, 1))) BETWEEN 65 AND 90
4 AND ASCII(UPPER(SUBSTR(DNAME, 2, 1))) BETWEEN 48 AND 57;
DEPTNO DNAME LOC
------ -------------- -------------
10 A1_BJ BEIJING
20 B2_SH SHANGHAI
40 D3 CHENGDU