Matching a pattern similar to this article
I am trying to use the LIKE clause in a SQL statement to match a specific pattern in Oracle.
I would like to do something like this:
LIKE '[A-Z][A-Z][1-4]%'
.. but I can't use regex because it's on Oracle9i (regex support came in 10g).
I am trying to match something that has two characters in front of it and then a number between 1 and 4 and whatever. I tried to do this, but it doesn't seem to work. The only way I could get it to work is by doing:
WHERE ...
LIKE '%1__' OR
LIKE '%2__' OR
LIKE '%3__' OR
LIKE '%4__'
I'm not sure if the way I would like to do this is possible or the correct way, as I have never tried to use templates with a LIKE clause.
Any help you could give would be greatly appreciated.
Try the following:
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 to match lower letters as well, apply the upper () function to the first substr (): eg. where upper (substr (c1,1,1)) in ...
Performance
I tested query performance with and without regexp_like. As you can see, the query without the regexp_like function was 100% faster. (Note: Both queries performed soft analysis)
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>
Method two
Get ascii values for 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 expression much 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
/
source to share
Awkward, but possible:
select *
from <your_table>
where TRANSLATE(SUBSTR(<blah>,1,3),'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234',
'AAAAAAAAAAAAAAAAAAAAAAAAAA1111') = 'AA1';
can meet your needs ....
EDIT: Inline xlnt suggestion from @Hobo for translating a substring, instead of taking a substring of the translated string ...
source to share
I recommend using INSTR :
INSTR(t.column, '1', 3, 1) > 0 OR
INSTR(t.column, '2', 3, 1) > 0 OR
INSTR(t.column, '3', 3, 1) > 0 OR
INSTR(t.column, '4', 3, 1) > 0
The wildcard issue in LIKE does not control where in the column the value 1/2/3/4 / etc will appear - it could be at the end.
DCookie is correct - this answer has no way of processing if it has numeric data in it. But it's still better than using LIKE with wildcards.
source to share
Shared wild card, but will offer it anyway.
Does your 9i database have PL / SQL Web Toolkit installed? The reason for the request is because one of our clients indicated that there is limited support for regular expressions using one of the packages that come with it.
The package is called owa_pattern and the only link I can find is one
I have never used it, and I am still trying to deal with regexps, so I cannot tell you if it suits your purpose, but thought it might be useful.
source to share
If you really want to use reg exps, you can create a Java stored procedure and accompanying pl / sql wrapping. (I am assuming the Java version supported in Oracle 9 supports reg exps, I'm not 100% sure). You can call this java file using pl / sql wrapper in your select statement.
But easier and faster:
SELECT c1
FROM t1
WHERE substr(c1,1,1) between 'A' and 'Z'
AND substr(c1,2,1) between 'A' and 'Z'
AND substr(c1,3,1) IN ('1','2','3','4')
A variant of the zürigschnäzlets solution without using an ascci function.
source to share