SBP SBP - 10 months ago 64
SQL Question

Oracle + finding match between semicolon-separated columns

I have a table with two columns A and B where the values are semi colon separated.

  • A has value

  • B has value

I want to check that if value in column B are all present in column A.

Please guide me.

Thanks !


You should fix your data structure. Storing lists of things as strings is very bad practice in SQL. The proper way to store a list is in a row in a different table, with one row per list item.

In your example, both lists are ordered alphabetically. If you know this is always true, you can do something like this:

select t.*
from t
where t.a like '%' || replace(t.b, ';', '%') || %;

This does not do what you want 100%, but it may be close enough for your purposes.