Querying Substrings Against A List Of Values

I'm reading from a dataset that I unfortunately don't have the access to modify. It has concatenated strings of values, and I want to select records for which any of those substrin

Solution 1:

You can use like:

select t.*from crappytable t
where';'|| crappycolumn ||';'like'%;2;%'or';'|| crappycolumn ||';'like'%;4;%';

You seem to know that storing lists of values in a single column is a bad idea, so I'll spare the harangue ;)

EDIT:

If you don't like like, you can use regexp_like() like this:

where regexp_like(';' || crappycolumn || ';', ';2;|;4;')

Solution 2:

A simpler method would be to use regexp_like to check if the list has 2 or 4 in it.

select *
from tablename
whereregexp_like(crappycolumn,'^[2|4][^0-9]|[^0-9][2|4][^0-9]|[^0-9][2|4]$|^[2|4]$')

• ^[2|4][^0-9] - Starts with 2 or 4 not followed by a digit.
• [^0-9][2|4][^0-9] - 2 or 4 not succeeded or preceded by a digit.
• [^0-9][2|4]$ - Ends with 2 or 4 not preceded by a digit.
• ^[2|4]$ - 2 or 4 is the only character in the string.

Solution 3:

Another form of regexp_like(). This regex looks for 2 or 4 only when proceeded by the beginning of the line or a semi-colon and when followed by a semi-colon or the end of the line:

SQL>with crappy_tbl(crappy_col) as (
     select'1;2'from dual unionselect'4'from dual unionselect'1'from dual unionselect'2;1'from dual unionselect'1;3'from dual unionselect'2'from dual unionselect'22;;44;'from dual
   )
   select crappy_col
   from crappy_tbl
   where regexp_like(crappy_col, '(^|;)(2|4)(;|$)');

CRAPPY_
-------1;222;14SQL>