-2

I have a database column that contains comma separated values and I want to search in that column by passing comma separated values and it should return that record if any of the given value matches.

For example: the column contains a,b,c,d,e, the input is d,c,h then it should return this record.

The sequence does not matter here and also its not necessary that all the values should match, even if single value matches then also record should be returned.

raj.rajpurohit6
  • 103
  • 7
  • 8
    Never, ever store data as comma separated items, it will only cause you lots of trouble. – jarlh May 07 '19 at 08:45
  • To add to @jarth ‘s comment [is storing a delimited list (comma separated list) in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Raymond Nijland May 07 '19 at 09:52
  • @RaymondNijland Yes it is more than "really that bad" because it causes extra complications that can easily be avoided by designing your database schema to use atomic values for each column. – Ken Evans May 07 '19 at 10:21

1 Answers1

0

As jarlh already stated you should not store data as comma separated items. I recommend you to read articles about database normalization on how to avoid these kind of data.

None the less you can do something like the following to query the desired records:

SELECT 1 FROM dual WHERE REGEXP_LIKE(','||'a,b,c,d,e'||',', ',('||REPLACE('d,c,h',',','|')||'),')

Note that you have to escape reserved characters in the input string before using this query...

Radagast81
  • 2,921
  • 1
  • 7
  • 21