CODEWITHSUNDEEP
sqlregexsql-server-2005
Pankaj Gupta
Pankaj Gupta

Reputation: 51

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.

Upvotes: 0

Views: 4906

Answers (3)

muffaddal shakir
muffaddal shakir

Reputation: 81

This can be easily achieve by CTE, REGEXP/REGEXP_Like/INSTR works better with oracle, for MS SQL Server you can try this

DECLARE @CSV VARCHAR(100) ='B2,B34,B31,B8,B4,B5,B33,B30,B20,B3'; SET @CSV = @CSV+',';

WITH CTE AS ( SELECT SUBSTRING(@CSV,1,CHARINDEX(',',@CSV,1)-1) AS VAL, SUBSTRING(@CSV,CHARINDEX(',',@CSV,1)+1,LEN(@CSV)) AS REM UNION ALL SELECT SUBSTRING(A.REM,1,CHARINDEX(',',A.REM,1)-1)AS VAL, SUBSTRING(A.REM,CHARINDEX(',',A.REM,1)+1,LEN(A.REM)) FROM CTE A WHERE LEN(A.REM)>=1 ) SELECT VAL FROM CTE WHERE VAL='B3'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271131

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

Upvotes: 3

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

Select *
FROM T
Where ',' + BNFT + ',' LIKE '%,B3,%';

or

Select *
FROM T
Where CHARINDEX  (',B3,',',' + BNFT + ',') > 0;

Upvotes: 0

Related Questions