巧妙利用ascii函数实现字符串匹配

虽然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