CODEWITHSUNDEEP
sqlregexoracle-database
shriekyphantom
shriekyphantom

Reputation: 143

Regex Lookahead in Oracle SQL

I'm trying to find certain strings in my tables. I need to display only the records that have those characters. For example I have this table:

TABLE_A
=================
 # | COL_A
=================
 1 | ABC
 2 | _ABC_
 3 | _ABCD_ABC_
 4 | ABCD

I want to select all records that has ABC but not ABCD. In the example table above, records 1, 2 and 3 should be displayed. The best I came up with now currently is this:

SELECT * FROM TABLE_A WHERE REGEXP_LIKE(COL_A, 'ABC[^D]{1,}', 'inm');

The problem with the statement above is that if there's only ABC at the end of the data, it won't be displayed.

PS: Is this even doable in pure SQL?

Upvotes: 1

Views: 2011

Answers (3)

Gary_W
Gary_W

Reputation: 10360

Look for rows where 'ABC' is preceeded by the start of the line or an underscore and proceeded by an underscore or the end of the line. 

SQL> with table_a(ID, col_a) as (
     select 1, 'ABC'        from dual union all
     select 2, '_ABC_'      from dual union all
     select 3, '_ABCD_ABC_' from dual union all
     select 4, 'ABCD'       from dual
   )
   select id, col_a
   from table_a
   where regexp_like(col_a, '(^|_)ABC(_|$)');

        ID COL_A
---------- ----------
         1 ABC
         2 _ABC_
         3 _ABCD_ABC_

SQL>

Upvotes: 0

MT0
MT0

Reputation: 167972

You can use [^D]|$ to match a non-D character or the end-of-line:

SELECT *
FROM   TABLE_A
WHERE  REGEXP_LIKE(COL_A, 'ABC([^D]|$)', 'i');

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You can do this with like:

where '_' || col_a || '_' like '%$_ABC$_%' escape '$'

However, storing multiple codes in a single column is not the SQLish way to store data. You should fix your data structure, particularly if you care at all about performance.

Upvotes: 0

Related Questions