CODEWITHSUNDEEP
regexoracle-databaseoracle12cregexp-like
ArtBajji
ArtBajji

Reputation: 960

Need Regular expression to match exact table name in source code in Oracle 12cR1

I am using Oracle 12cR1. I need a REGEX pattern to exactly match a given table name from Oracle source code (i.e) the match must not be preceded or succeeded by a number or an alphabet or an underscore symbol.

I have created a dummy source code and tried the below. It is not fetching all the desired records. What am I missing? Is there a REGEX pattern for this?

Test code:

with names as (
select 'mytabname' tabname from dual union all
select 'mytabname1' from dual union all
select 'mytabname_' from dual union all
select 'mytabnameX' from dual union all
select '1mytabname' from dual union all
select '_mytabname' from dual union all
select 'Xmytabname' from dual union all
select '1mytabname_' from dual union all
select '1mytabnameX' from dual union all
select '_mytabname1' from dual union all
select 'Xmytabname1' from dual union all
select '_mytabnameX' from dual union all
select 'Xmytabname_' from dual union all
select '_mytabname_' from dual union all
select 'XmytabnameX' from dual union all
select '1mytabname1' from dual union all
select ' mytabname_' from dual union all
select '_mytabname ' from dual union all
select ' mytabname ' from dual union all
select ' mytabnameX' from dual union all
select 'Xmytabname ' from dual union all
select ' mytabname1' from dual union all
select '1mytabname ' from dual union all
select '.mytabname ' from dual union all
select ' mytabname.' from dual union all
select '.mytabname.' from dual union all
select '*mytabname' from dual union all
select ' mytabname*' from dual union all
select '*mytabname*' from dual union all
select 'defmytabnameabc' from dual)
select 'start'||tabname||'end' from names
where regexp_like(tabname, '[^a-zA-Z0-9_](mytabname)[^a-zA-Z0-9_]');

Output:

start mytabname end
start.mytabname end
start mytabname.end
start.mytabname.end
start mytabname*end
start*mytabname*end

The output misses the following records

startmytabnameend
start*mytabnameend

Upvotes: 0

Views: 114

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

I generally use this:

SELECT *
  FROM user_source
 WHERE regexp_like(text, '(\W|^)' || 'employees' || '(\W|$)', 'i');

find the details of Regex here: https://stackoverflow.com/a/52441424/7998591

'i' is for ignore case and you may omit if you wish.

Demo for your data

Upvotes: 1

Related Questions