0

I have a comma separated values for each row in table. Suppose in test table

Value
1,5,12
2,4,15
5,2,6

I would like to find records with value =5 Using normal ms sql, I had used like operator which gave me all the 3 records. Could you please help if can it be possible with regular expression.

PNG
  • 23
  • 5
  • Don't store data as comma separated items, it will only cause you lots of trouble. – jarlh Nov 22 '18 at 10:27
  • 1
    Are you using MySQL or MS SQL Server? – jarlh Nov 22 '18 at 10:27
  • MySQL you can use : **SELECT ..... WHERE CONCAT(',',`value`,',') LIKE '%,5,%';** - but it is always a FULL TABLE SCAN. see @ jarlh – Bernd Buffen Nov 22 '18 at 10:31
  • Possible duplicate of [Is storing a delimited 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) – Madhur Bhaiya Nov 22 '18 at 10:41
  • @jarlh I am using mssql – PNG Nov 22 '18 at 10:54
  • The table is already created in application. So couldn't change. Using regular expressions whether we can get a solution? – PNG Nov 22 '18 at 11:01

2 Answers2

0

Try this too. Does not look very efficient but working.

    /* Create a table called NAMES */
   CREATE TABLE test(Id integer PRIMARY KEY, Name text);

  /* Create few records in this table */
 INSERT INTO test VALUES(1,'1,5,12');
 INSERT INTO test VALUES(2,'2,4,15');
 INSERT INTO test VALUES(3,'5,2,6');
 INSERT INTO test VALUES(5,'1,2,5');
-- SELECT * FROM test;

SELECT * from test WHERE Name LIKE '%,5,%' OR 
 Name LIKE '%5,%' OR 
 Name LIKE '%,5%';
Prashant Deshmukh.....
  • 2,244
  • 1
  • 9
  • 11
0

You probably already know this is a bad data format and you should fix it. If you are stuck with it, you can use like:

SELECT t.* 
FROM test t
WHERE ',' + Name + ',' LIKE '%,5,%';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786