How to create SQL Query with like item1, item2, item3 format at Delphi?

Question

I am using CheckListEdit component at Delphi which enables to select more than one item at time. When extracted using CheckListEdit1.Text, I get selected items in the String format like [item1, item2, item3] which I trimmed further to discard first and last character to get final string as 'item1, item2, item3'.

My code is as follows

glist := CheckListEdit1.Text;
glistindex := Length(glist);
Delete(glist,glistindex,1);
Delete(glist,1,1);

//Here I am getting glist = 'item1, item2, item3..' etc..

Now I want to select from data from table where group like item1, item2, item3 etc..

My SQL Query is as follows

UniTable1.SQL.Text := Select * from emp where group like (:grp);
UniTable1.Params.ParamByName('grp').Value := glist;

But above SQL is returning error as 'like item1, item2, item3' is not a proper format is there any alterante way to create SQL query so that it will return data from selected group at CheckListEdit ?

This is sadly one of the cases where you have to build your query without using parameters... — whosrdaddy, Dec 11 '13 at 08:57
The Like operator doesn't support multiple arguments. I don't suppose the In operator would suit your needs? — Andy_D, Dec 11 '13 at 09:34
Here is [one way](http://stackoverflow.com/a/5401777/800214) to deal with this problem: — whosrdaddy, Dec 11 '13 at 09:40
I do not know the DB components that you are using but check if they have a Macro `property`. — Wodzu, Dec 11 '13 at 10:49

score 2 · Accepted Answer · answered Dec 11 '13 at 09:31

2

Instead of building a string as 'item1, item2, item3', you need to build it as '(item1, item2, item3)'. Then your query will become

Select * from emp where group in (item1, item2, item3)

As whosrdaddy pointed out, you will have to build the query as a concatenated string; it's not possible to pass the list of values as a parameter.

answered Dec 11 '13 at 09:31

No'am Newman

6,395
5
38
50

When I wrote UniTable1.SQL.Text := 'Select * from emp where pgroup in (officer, manager, clerk)' ... it is giving error like 'No Such Column: officer'. – Ninad Avasare Dec 11 '13 at 11:42
If I wrote something like this it works UniTable1.SQL.Text := 'Select * from emp where pgroup in (''officer'','' manager'', clerk'')' – Ninad Avasare Dec 11 '13 at 11:55
1

It depends on your database. I normally only use integers (ie keys) in such a query ('where pgroup in (1,2,3)'). I would think that alphanumeric values would have to be passed in single quotes, not doubles, which would mean that in your program you would have to write two single quotes before and after each value. It's best to check the query with some command line tool first. – No'am Newman Dec 11 '13 at 14:12
@Noám Newman, it worked finally with string value with slight change in above query without using parameters UniTable1.SQL.Text := 'Select * from emp where pgroup in ('+ glist +')' – Ninad Avasare Dec 11 '13 at 15:47

score 2 · Answer 2 · answered Dec 11 '13 at 11:09

Using parameter in the IN operator is not correct.You should either use an IN parameter list (:p1, :p2, ..., :pN) or use macros:

UniQuery.SQL.Text := 'Select * from emp where group in (&grp)';
UniQuery.MacroByName('grp').AsString := glist;
UniQuery.MacroByName('grp').Active := true;

How to create SQL Query with like item1, item2, item3 format at Delphi?

2 Answers2