Pankaj Gupta Pankaj Gupta - 1 month ago 12
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.


Example

Columns BNFT has text values such as


B20,B30,B3,B13,B31,B14,B25,B29,B1,B2,B4,B5
OR
B1,B2,B34,B31,B8,B4,B5,B33,B30,B20,B3



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 *
FROM T
Where BNFT LIKE '%B3,%' or BNFT LIKE '%B3'


Pls advise


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

Answer

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