Pattern Matching with Like Clause

sql oracle plsql oracle9i
user152759 picture user152759 · Sep 30, 2009 · Viewed 11.3k times · Source

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

DCookie picture DCookie · Sep 30, 2009

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...

Related questions

Remove all characters after a specific character in PL/SQL

How do I get a substring from this example value: john.abc_1234 I want it to return john.abc.So basically we need to remove all the information after _. More examples: 1234_abc

Read More
Single SQL SELECT Returning multiple rows from one table row

We have a table which is of the form: ID,Value1,Value2,Value3 1,2,3,4 We need to transform this into. ID,Name,Value 1,'Value1',2 1,'Value2',3 1,'Value3',4 Is there a clever way of doing this in one SELECT statement (…

Read More
count number of rows that occur for each date in column date range

I have a table with data such as below Group Start Date End Date A 01/01/01 01/03/01 A 01/01/01 01/02/01 A 01/03/01 01/04/01 B 01/01/01 01/01/01 ETC I am looking to produce a view that gives a count for each day, like this Group Date Count A 01/01/01 2 A 01/02/01 2 …

Read More