Pattern Matching in Oracle SQL

Question

I am trying to match some patterns and exclude some based on certain characters and lengths.

I want to return all 3 characters and 4 characters values:

• The 3 character values should have any letter at 3rd position i.e 11A
• The 4 character values should have 2 letters at the end i.e 11AB, but shouldn't have letters EF i.e need to exclude values like 11EF.

Table:

+------+
| CODE |
+------+
|   11 |
|  11A |
|  11B |
| 11EF |
| 11AB |
+------+

EXPECTED OUTPUT:

+-----------------+
| EXPECTED OUTPUT |
+-----------------+
|             11A |
|             11B |
|            11AB |
+-----------------+

I've tried this but it returns all regardless of the conditions I mentioned above:

select REGEXP_SUBSTR('11EF','^[0-9]{1,2}[A-Z]{1,2}?')  from dual;

select REGEXP_SUBSTR('11','^[0-9]{1,2}[A-Z]{1,2}?')  from dual;

select REGEXP_SUBSTR('11A','^[0-9]{1,2}[A-Z]{1,2}?')  from dual;

Answer 1

This uses a case-insensitive REGEXP_LIKE() that matches a string consisting of 2 characters followed by a letter OR 2 characters followed any letter except E, followed by any letter except F.

with tbl(code) as (
  select '11' from dual union all
  select '11A' from dual union all
  select '11b' from dual union all
  select '11EF' from dual union all
  select '11FE' from dual union all
  select '11A#' from dual union all
  select '11BA' from dual union all
  select '1ZZZ' from dual union all  
  select NULL from dual union all
  select 'HELLO' from dual union all
  select 'A12' from dual
)
select code
from tbl
where regexp_like(code, '^((.{2}[A-Z])|(.{2}[A-D,F-Z][A-E,G-Z]))$', 'i');



CODE 
-----
11A  
11b  
11FE 
11BA 
1ZZZ 

5 rows selected.

EDIT: could be simplified a little to:

'^.{2}(([A-Z])|([A-D,F-Z][A-E,G-Z]))$'

Answer 2

This worked for me on Oracle Live SQL:

create table temp( text varchar2(25));

INSERT INTO temp values ('11');
INSERT INTO temp values ('11A');
INSERT INTO temp values ('11B');
INSERT INTO temp values ('11EF');
INSERT INTO temp values ('11AB');
INSERT INTO temp values ('21ASDF');
INSERT INTO temp values ('31ASD');
INSERT INTO temp values ('251ASDF');
INSERT INTO temp values ('41ASDFDSF');

select text
from temp
where length(text) between 3 and 4
and (regexp_instr(text, '[[:alpha:]]', 3) > 0 or regexp_instr(text, '[[:alpha:]][[:alpha:]]', 3) > 0)
and substr(text, 3, 2) <> 'EF';

TEXT
11A
11B
11AB

Answer 3

I changed your regular-expression to this. It does not perform the EF-check in the regex but elsewhere in the query as that would make the regex far more complicated:

^(\d\d[A-Z])|(\d\d[A-Z]{2})$

And this query works for me in SqlFiddle.com:

SELECT
  text_col
FROM
  Foo
WHERE
  REGEXP_INSTR( text_col, '^(\d\d[A-Z])|(\d\d[A-Z]{2})$' ) > 0
  AND
  REGEXP_INSTR( text_col, 'EF$' ) = 0