Pankaj Gupta Pankaj Gupta - 7 months ago 39
SQL Question

Search for a particular value in a string with commas

I have a TEXT column in my Table T and contains some values separated by Commas.


Columns BNFT has text values such as


I want to return result in my query only if B3 is present.
It should not consider B30-B39 or B[1-9]3 (i.e. B13, B23 .... B93).

I tried with below query, but want to implement REGEXP or REGEXP_LIKE/INSTR etc. Haven't used them before and unable to understand also.

Select *
Where BNFT LIKE '%B3,%' or BNFT LIKE '%B3'

Pls advise

Procedures will not work. Query must start with Select as 1st statement.


The first advice is to fix your data structure. Storing lists of ids in strings is a bad idea:

  • You are storing numbers as strings. That is the wrong representation.
  • You are storing multiple values in a string column. That is not using SQL correctly.
  • These values are probably ids. You cannot declare proper foreign key relationships.
  • SQL does not have particularly strong string functions.
  • The resulting query cannot take advantage of indexes.

That said, sometimes we are stuck with other people's bad design decisions.

In SQL Server, you would do:

where ',' + BNFT + ',' LIKE '%,33,%'

This question was originally tagged MySQL, which offers find_in_set() for this purpose:

Where find_in_set(33, BNFT) > 0