CODEWITHSUNDEEP
sqlregexoracle-database
infi999
infi999

Reputation: 21

regexp_substr for getting correct result

I was trying to use regexp_substr to get each correct column name from a column list string.

The query is like:

select regexp_substr(v_keep, '(^|[(,) ]*)' || r.column_name || '($|[(,) ]+)', 1, 1, 'i')
from dual;

But the result is not correct.

The v_keep can be any column name list like abc, abc_abc, abc1 or (abc, abc_abc, abc1).

The r.column_name can be like abc or ab.

- If the input v_keep is (abc, abc_abc, abc1) and the r.column_name is ab, it will return null.

- If the input v_keep is (abc, abc_abc, abc1) and the r.column_name is abc, it will return the column name just abc.

Can anyone help me to fix it by just modify the pattern inside the regexp_substr ?

Upvotes: 0

Views: 79

Answers (3)

Gary_W
Gary_W

Reputation: 10360

Since this is PL/SQL code to see if a value is in a string, try this which avoids the overhead of hitting the database, and calling REGEXP. Just keep it straight SQL. I hate the nested REPLACE calls but I was trying to avoid using REGEXP_REPLACE although it could be done in one call if you did use it.

set serveroutput on;
set feedback off;

declare 
  v_keep  varchar2(50) := '(abc, abc_abc, abc1)';
  compare varchar2(10) := 'abc_';
begin
  if instr(',' || replace(replace(replace(v_keep, ' '), '('), ')') || ',', ',' || compare || ',') > 0 then
    dbms_output.put_line('Column ''' || compare ||''' IS in the keep list');
  else
    dbms_output.put_line('Column ''' || compare ||''' IS NOT in the keep list');
  end if;  
end;

Upvotes: 0

kfinity
kfinity

Reputation: 9091

It's pretty simple, you just need to add a subexpression so you can pull out the part of the string you want. (A subexpression is a section of the regexp in parentheses.) In this case the last argument is 2, because you want the part of the match that corresponds to the second group of parentheses.

regexp_substr(v_keep, '(^|[(,) ]*)(' || r.column_name || ')($|[(,) ]+)', 1, 1, 'i', 2)

Gordon's solution will have better performance, though.

Edit: working example - 

with testdata as (select '(abc, abc_abc, abc1)' as v_keep, 'abc' as column_name from dual)
select regexp_substr(v_keep, '(^|[(,) ]*)(' || r.column_name || ')($|[(,) ]+)', 1, 1, 'i', 2)
from testdata r;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Why not just use a case and like?

select (case when replace(replace(v_keep, '(', ','), '(', ',')) like '%,' || r.column_name || ',%'
             then r.column_name
        end)

I don't recommend storing lists in a comma-delimited string, but if you are, this is one way to identify individual elements of the list.

Upvotes: 2

Related Questions