Pattern Matching with Like Clause

Question

I am attempting to use a LIKE clause in a SQL statement to match a certain pattern within Oracle.

I would like to do something as such:

LIKE '[A-Z][A-Z][1-4]%'

..but I can't use a regex because this is on Oracle9i (regex support came in 10g).

I am attempting to match something that has two characters before it, then a number between 1 and 4 and that whatever beyond that. I have attempted this, but it doesn't seem to work. The only way I have been able to get it to work is by doing:

WHERE ...
LIKE '%1__' OR 
LIKE '%2__' OR 
LIKE '%3__' OR 
LIKE '%4__'

I am not sure if the way I would like to do it is possible or the correct way as I have never attempted patterns with the LIKE clause.

Any help you could give would be greatly appreciated.

Answer 1

Clunky, but perhaps:

select *
  from <your_table>
 where TRANSLATE(SUBSTR(<blah>,1,3),'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234',
                                    'AAAAAAAAAAAAAAAAAAAAAAAAAA1111') = 'AA1';

might suit your needs....

EDIT: Incorporated xlnt suggestion by @Hobo to translate the substring, rather than taking the substring of the translated string...

Answer 2

Try this:

SELECT c1
FROM   t1
WHERE  substr(c1,1,1) IN ('A','B','C','D',
                          'E','F','G','H',
                          'I','J','K','L',
                          'M','N','O','P',
                          'Q','R','S','T',
                          'U','V','W','X',
                          'Y','Z')
       AND substr(c1,2,1) IN ('A','B','C','D',
                              'E','F','G','H',
                              'I','J','K','L',
                              'M','N','O','P',
                              'Q','R','S','T',
                              'U','V','W','X',
                              'Y','Z')
       AND substr(c1,3,1) IN ('1','2','3','4')
/

If you want also to match lower letters, then apply the upper() function to the 2 first substr(): eg. where upper(substr(c1,1,1)) in ...

Performance

I tested the Performance of the query with regexp_like and without. As you can see the query without the regexp_like function was 100% faster. (Note. Both querys did a soft-parse)

SQL> select count(*) from t1;                                                                                                                     

  COUNT(*)
----------
    458752

Elapsed: 00:00:00.02
SQL> set timing off;                                                                                                                                  
SQL> select count(*) from t1;                                                                                                                     

  COUNT(*)
----------
    458752

SQL> set timing on;                                                                                                                                   
SQL> select count(*) from t1 where regexp_like(c1, '[A-Z][A-Z][1-4].*');                                                                       

  COUNT(*)
----------
     65536

Elapsed: 00:00:02.66
SELECT count(*)
FROM   t1
WHERE  substr(c1,1,1) IN ('A','B','C','D',
                          'E','F','G','H',
                          'I','J','K','L',
                          'M','N','O','P',
                          'Q','R','S','T',
                          'U','V','W','X',
                          'Y','Z')
       AND substr(c1,2,1) IN ('A','B','C','D',
                              'E','F','G','H',
                              'I','J','K','L',
                              'M','N','O','P',
                              'Q','R','S','T',
                              'U','V','W','X',
                              'Y','Z')
       AND substr(c1,3,1) IN ('1','2','3','4')
 18  /                                                                                                                                                

  COUNT(*)
----------
     65536

Elapsed: 00:00:01.15
SQL> 

Second Method

Get the ascii values of A,Z,1 and 4

SQL> select ascii('A') from dual;                                                                                                                     

ASCII('A')
----------
        65


SQL> select ascii('Z') from dual;                                                                                                                     

ASCII('Z')
----------
        90


SQL> select ascii('1') from dual;                                                                                                                     

ASCII('1')
----------
        49

SQL> select ascii('4') from dual;                                                                                                                     

ASCII('4')
----------
        52

Now you can write your statement a lot shorter

SELECT count(* )
FROM   t1
WHERE  ascii(substr(c1,1,1)) BETWEEN 65 AND 90
       AND ascii(substr(c1,2,1)) BETWEEN 65 AND 90
       AND ascii(substr(c1,3,1)) BETWEEN 49 AND 52
/