CODEWITHSUNDEEP
sqloperatorsfirebirdsql-in
Hanik
Hanik

Reputation: 327

working principle of IN operator in sql

I have a varchar column like 'a,b,c,d,cd,cx,bx'. This has comma for separating some prefix code and I want to search c prefix in this column. Column name is prefix.

When we use IN operator:

select * from prefixes where 'c' IN (prefix)

it does work but how? I know that prefix must 'a', 'b', 'c', 'd', 'cd', 'cx', 'bx' for work to IN operator like:

select * from prefixes where 'c' IN ('a', 'b', 'c', 'd', 'cd', 'cx', 'bx')

Am I wrong? And how can it work? Commas are not separator. Those are in string like prefix.

Upvotes: 0

Views: 129

Answers (2)

Mr. Nice
Mr. Nice

Reputation: 417

The same way below query work

SELECT *
FROM some_table
WHERE 'a' = 'a'

OR another examples: 1 = 1, 1 < 5, 'a' IN ('abc'). It will all work.

Query will go through all records in the table and when it checks the condition (WHERE) it is always true. It will return all data in your table because you have static condition which is always true.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521168

Here is a general query which would work:

SELECT *
FROM yourTable
WHERE ',' || col || ',' LIKE '%,c,%';

The trick here is to search for ,c, in the slightly modified column value ,a,b,c,d,cd,cx,bx,. We add commas to the beginning and end of the column CSV value so that each term can be uniquely addressed with comma separators.

While this gives you a workaround, you should view it as temporary. The best long term solution is to not store CSV in your tables.

Upvotes: 1

Related Questions