What is the equivalent function in sqlite for find_in_set(value,set) in MySQL?

Question

The example below is w.r.t MySQL. For eg: test1 is as follows

+----+------------------------+
| id | name                   |
+----+------------------------+
|  2 | 1,2                    |
| 33 | 2,44,33,5              |
|  1 | 11,4,55                |
| 12 | 11,111,122,551,112,221 |
+----+------------------------+

select * from test1 where find_in_set('122',name)

will o/p the following:

+----+------------------------+
| id | name                   |
+----+------------------------+
| 12 | 11,111,122,551,112,221 |
+----+------------------------+

In Sql Lite, using like it would be:

select * 
from `test1` 
where name like '%,122,%'
or name like '122,%'
or name like '%,122'
or name = '122'

A case:

My value is 551,122. In this case,as value is returned from appln we can split the value column & write the query as

(',' || column_name || ',') LIKE '%,551,%' or
(',' || column_name || ',') LIKE '%,122,%'

any best idea to handle this ?

I wanna avoid like. So any other idea?

Answer 1

My first idea is that you shouldn't store CSV data in a column in a relational database, you should use a separate association table.

You can make this work with LIKE though. Your comment indicates that you see the three cases but there are actually four:

select *
from test1
where name like '122,%'
   or name like '%,122'
   or name like '%,122,%'
   or name    = '122'     /* You forgot about this degenerate case */

An index can be used for the last case and possibly for the first but the middle two will probably be table scans. You can simplify the query if you force your data to always have the leading and trailing commas:

+----+--------------------------+
| id | name                     |
+----+--------------------------+
|  2 | ,1,2,                    |
| 33 | ,2,44,33,5,              |
|  1 | ,11,4,55,                |
| 12 | ,11,111,122,551,112,221, |
+----+--------------------------+

Then you can use just one LIKE (which won't use an index):

select *
from test1
where name like '%,122,%'

But you really should use an association table so that you could use where name = 122 and join to other tables for the rest.