Search a pattern from comma seperated parameters in plsql-CodePudding

My Parameter to a procedure lv_ip := 'MNS-GC%|CS,MIB-TE%|DC'

My cursor query should search for records that start with 'MNS-GC%' and 'MIB-TE%'.

Select id, date,program,program_start_date
from table_1
where program like 'MNS-GC%' or program LIKE 'MIB-TE%'

Please suggest ways to read it from the parameter and an alternative to LIKE.

CodePudding user response：

If you're stuck with that input and the structure is fixed, with each comma-separated element having a pipe-delimited value, you could possibly convert that string to a regular expression pattern, and then use regexp_like to pattern-match:

select id, date, program, program_start_date
from table_1
where regexp_like(
  program,
  '^(' || rtrim(regexp_replace(lv_ip, '%\|.*?(,|$)', '|'), '|') || ')')

With your example parameter, the

'^(' || rtrim(regexp_replace(lv_ip, '%\|.*?(,|$)', '|'), '|') || ')'

would generate the pattern

^(MNS-GC|MIB-TE)

i.e. looking for either of those strings at the start of the program value.

db<>fiddle

Alternatively you could split the input up yourself, with instr and substr, and - since the number of elements may vary - create a dynamic query using them. That might be faster than using regular expression, but might be harder to maintain.

CodePudding user response：

Since you mention you want to preserve what's on the right side of the pipe, and want to be able to process parameters dynamically, here's a way to parse multi-delimited data that could give you some ideas using a CTE.

The table called 'tbl' just sets up your original data. tbl_comma contains that data split on the comma. The final query splits that data into name/value pairs.

Hopefully this will help give you some ideas even though it's not the exact answer you are looking for.

COLUMN ID FORMAT a3
COLUMN PROGRAM FORMAT a10
COLUMN part2 FORMAT a6

-- Original data
WITH tbl(ID, DATA) AS (
  SELECT 1, 'MNS-GC%|CS,MIB-TE%|DC' FROM dual UNION ALL
  SELECT 2, 'MNS-GC%|CS,MIB-TE%|DC,MIB-TA%|AB,MIB-TB%|BC' FROM dual
),
tbl_comma(ID, CASE) AS (
SELECT ID,
       REGEXP_SUBSTR(DATA, '(.*?)(,|$)', 1, LEVEL, NULL, 1) CASE
FROM tbl
CONNECT BY REGEXP_SUBSTR(DATA, '(.*?)(,|$)', 1, LEVEL) IS NOT NULL 
  AND PRIOR ID = ID
  AND PRIOR SYS_GUID() IS NOT NULL        
)  
--SELECT * FROM tbl_comma;         
-- Parse into name/value pairs
SELECT ID,
       REGEXP_REPLACE(CASE, '^(.*)\|.*', '\1') PROGRAM,
       REGEXP_REPLACE(CASE, '.*\|(.*)$', '\1') PART2
FROM tbl_comma;


 ID PROGRAM    PART2 
--- ---------- ------
  1 MNS-GC%    CS    
  1 MIB-TE%    DC    
  2 MNS-GC%    CS    
  2 MIB-TE%    DC    
  2 MIB-TA%    AB    
  2 MIB-TB%    BC    

6 rows selected.