Why `"%,2,%" like ",1,2,3,"` return zero?

Question

I got a SQL not working as expected. And I found out it's because of one of the like clause.

I expected the following sql

select * from SomeTable Where "%,2,%" like ",1,2,3,";

gives the same result as

select * from SomeTable;

but it actually gives nothing. I can't expand ",1,2,3," and I can't use find_in_set because I'm using memsql. So is there any other way I can do to determine an entry is in a comma separated list string, or did i do anything wrong?

Answer 1

I think you actually meant that

select * from SomeTable Where ",1,2,3," LIKE "%,2,%" ;

Gives the same result as SELECT * SomeTable. Because ",1,2,3," like "%,2,%" always evaluates to TRUE so. The above statement is indeed the same as

select * from SomeTable;

On the other hand in the following statement

select * from SomeTable Where "%,2,%" like ",1,2,3,";

The "%,2,%" like ",1,2,3," component always evaluates to False. This will give you zero results.

I think what you are really looking for is:

 select * from v Where somecolumn like "%,2,%"