2

I have a column that is a text string that contains a comma separated list of ids. It could look something like this:

1,2,3,45,65,23245,564747,23,121,54,65,345678

I need to find if it contains a specific id or not. To get if it just contains a number would be fairly straightforward to do with a regular expression, but the problem is that for example the id 4 is not in the list but of lot of the ids contains a 4. Maybe it's still possible to write a regular expression that can handle that, but that's above my level. Or is there another way to solve this in SQL?

EDIT

Just to clarify, I don't actually have a column containing a comma separated list. What I have is a listagg that is made from several rows from a query, and I want to be able to apply a filter to it. I just tried to simplify the question so it would be more focused on the actual problem. I didn't realize that doing this would result in more answers on the database design than on the actual question.

Jesper Evertsson
  • 613
  • 9
  • 28
  • If you have comma-separated lists in your SQL database, you have an error in your database design (i.e. you are missing a table). Fix the error, not the query. – Tomalak Jun 26 '17 at 13:22
  • 2
    This has been asked here at least 100 times, I just linked to one such thread. The accepted answer there is also the accepted answer in half the threads at least. No need to reinvent the wheel. –  Jun 26 '17 at 13:26

2 Answers2

5

As others said, comma separated values are bad, but if you want anyway, this is one way:

select * from t where REGEXP_LIKE (col , '(^|,)4(,|$)')
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
4

Storing values in a comma separated list is a very bad design pattern, it gives nothing but headaches. If you can, normalize it.

If you can't, you can search for a specific ID using the commas in the comparison:

WHERE [Column] LIKE '%,4,%'
   OR [Column] LIKE '4,%'
   OR [Column] LIKE '%,4'
HoneyBadger
  • 14,750
  • 3
  • 34
  • 48