SBP SBP - 5 months ago 23
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
    'a;b;c;d;e'

  • B has value
    'a;b;d;e'



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

Please guide me.

Thanks !

Answer

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.