CODEWITHSUNDEEP
sqlitesql-like
doc_noob
doc_noob

Reputation: 625

sqlite query for getting a value from a column containing multiple values

I have a simple table that looks like below

enter image description here

I need to find the 'ID' which has the Number 3, so i wrote a query like below

select * from IDtable where Number like '%3%'

it is actually returning all the ID since i have used like and the Number contains many values starting with 3, how do i get the id which contains 3

Upvotes: 1

Views: 364

Answers (1)

forpas
forpas

Reputation: 164099

Concatenate a , at the start and at the end of the column and check if it contains ',3,' with the operator LIKE:

SELECT * 
FROM IDtable 
WHERE ',' || Number || ',' LIKE '%,3,%'

or with INSTR():

SELECT * 
FROM IDtable 
WHERE INSTR(',' || Number || ',', ',3,')

In Python, you should use a ? placeholder for the parameter "3":

n = "3"
sql = """
SELECT * 
FROM IDtable 
WHERE ',' || Number || ',' LIKE '%,' || ? || ',%'
"""
cursor.execute(sql, (n,))

Note that a normalized table like:

ID Number
Ab 2
Ab 9
Ab 16
... ......
cD 3
cD 10
cD 17
... ......

would save you all the trouble of querying with a complicated string expression which may prove bad for performance.

Upvotes: 1

Related Questions