regexp_substr with LIKE as search condition

Question

Thank you mathguy for your suggestion and assistance. The example you provided is a near perfect description of the issue. That being said I've used and edited your text to help describe this issue:

I receive a string that contains comma delimited digits in the form of 18656, 16380, 16424 (call this param1). The string only contains commas and digits. In mytable I have a column named t with values such as 18656.01.02, 10.02.02, 16380.02.03, 16424.05.66, 16424.55.23.14. I want to select the all rows that match all of the comma-separated digits in param1; where the first numeric component in column t is like 18656, 16380, 16424. Is there a way to use regexp_substr in this case.

Where param1 = 18656, 16380, 16424 the following works:

select * from mytable where t.mycolumn IN
    (
    (SELECT regexp_substr(:param1,'[^,]+', 1, level) as NUMLIST
    FROM DUAL
     CONNECT BY regexp_substr(:param1, '[^,]+', 1, level) IS NOT NULL)
    );

How to use wildcard if data I seek from t.mycolumn = 18656.00.01, 16380.09.34, 16424.023.8 Can LIKE be used as search criteria? If possible please provide example. Obviously, the following will not work but I am hoping to find a solution.

  select * from mytable where t.mycolumn LIKE
    (
    (SELECT regexp_substr(:param1||'%','[^,]+', 1, level) as NUMLIST
    FROM DUAL
     CONNECT BY regexp_substr(:param1||'%', '[^,]+', 1, level) IS NOT NULL)
    );

Clear as mud. Without using ANY code, can you explain the problem? For example: "I receive a string, call it param1. Here are a few examples. The string may contain commas, and it may also contain spaces." (Or not!) "Other than that it's digits. Now, in a table I have a column with string values like '1930.2.11'. I want to select the rows where the first numeric component in the column value, like 1930 in this example, matches one of the comma-separated numbers in the parameter." Or whatever your requirement is, just don't use any code, SQL or otherwise, to define the problem. — , Jan 06 '17 at 21:00
I agree with @mathguy. But at the very least tell us the desired output. What about it doesn't work? What is the desired output? — Chris Hep, Jan 06 '17 at 21:34
If you had normalized data, this problem would've been much easier to solve — Gurwinder Singh, Jan 07 '17 at 05:37

score 0 · Answer 1 · answered Jan 07 '17 at 06:26

Assumptions:

There is a table named mytable with a column named t which contains values as follows:

SELECT * FROM mytable;

T              |
---------------|
18656.01.02    |
10.02.02       |
16380.02.03    |
16424.05.66    |
16424.55.23.14 |

There is a string received as a parameter, that contains comma delimited digits in the form of 18656, 16380, 16424. The string only contains commas and digits. This string is parsed into indyvidual rows with a help of a query that looks similar to the folowing one:

SELECT regexp_substr(param1,'[^,]+', 1, level) as NUMLIST
FROM ( 
   select '18656,16380,16424' as param1 FROM DUAL
) 
CONNECT BY regexp_substr(param1, '[^,]+', 1, level) IS NOT NULL
;

NUMLIST |
--------|
18656   |
16380   |
16424   |

Requirement

Can LIKE be used as search criteria? If possible please provide example.

LIKE keyword is used below as a condition in JOIN ... ON clause:

SELECT * FROM mytable
WHERE t IN (
   SELECT t 
   FROM mytable m
   JOIN (
            SELECT regexp_substr(param1,'[^,]+', 1, level) as NUMLIST
            FROM ( 
               select '18656,16380,16424' as param1 FROM DUAL
            ) 
            CONNECT BY regexp_substr(param1, '[^,]+', 1, level) IS NOT NULL
   ) x
   ON m.t LIKE '%' || x.NUMLIST || '%'
 )

T              |
---------------|
18656.01.02    |
16380.02.03    |
16424.05.66    |
16424.55.23.14 |

Thank you very much krokodilko! Works perfectly. – BJC Jan 08 '17 at 22:30 — BJC, Jan 08 '17 at 22:30

regexp_substr with LIKE as search condition

1 Answers1

Assumptions:

Requirement