0

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Hanik
  • 317
  • 2
  • 6
  • 25
  • [Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) – Lukasz Szozda Jun 11 '18 at 07:51
  • Which version of SQL are you using? – Tim Biegeleisen Jun 11 '18 at 07:53
  • firebird 2.5. i tested it in ibexpert – Hanik Jun 11 '18 at 07:54
  • 1
    Never, ever store data as comma separated items. It will only cause you lots of trouble. – jarlh Jun 11 '18 at 08:01
  • @jarlh i agree with you and this query should not work normally i think but it works. how ? – Hanik Jun 11 '18 at 08:03
  • @Hanik There is a difference between the CSV single _string_ `a,b,c,d,cd,cx,bx` and the _list_ of strings `('a', 'b', 'c', 'd', 'cd', 'cx', 'bx')`. They are not the same thing. – Tim Biegeleisen Jun 11 '18 at 08:48
  • Did you mean to say _"it does **not** work"_? Otherwise, I'm not sure what your question is. – Mark Rotteveel Jun 11 '18 at 09:02
  • Frankly, it is very hard to understand your issue. What data you start with? What commands you run? Which results did you expect? Why did you expect those results not others? Which results came out really? Why do you think those results are not expected? What is so in those actual results that does not match your expectations and why? We do not get information to understand what is your question. https://www.chiark.greenend.org.uk/~sgtatham/bugs.html – Arioch 'The Jun 13 '18 at 09:45

2 Answers2

1

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • i know that but i want to learn it how it can work the query in question ? how ? – Hanik Jun 11 '18 at 07:56
  • @Hanik I have answered your question to the best of my ability. You should spend some time trying to understand it. – Tim Biegeleisen Jun 11 '18 at 07:57
  • thank you so much but i don't want any working query. i want to learn how " select * from prefixes where 'c' IN (prefix) " query work ? how ? prefix has a string has some commas – Hanik Jun 11 '18 at 07:59
  • @Hanik My query is probably as good as you will get. Don't store CSV in your tables. – Tim Biegeleisen Jun 11 '18 at 08:46
0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mr. Nice
  • 357
  • 2
  • 16