DevArt dotConnect for Oracle - how to do a WHERE LIKE clause with a parameter?

Question

How do I do a WHERE COLUMN LIKE '%SearchTerm%' with the DevArt dotConnect for Oracle libraries?

Ideally using a CommandParameter for the SearchTerm?

I've tried the following with no joy:

cmd.CommandText =
  "SELECT ID, COLUMN_A, COLUMN_B FROM TABLE_A
   WHERE COLUMN_A LIKE :SearchTerm";

I've got it working with:

cmd.CommandText =
  "SELECT ID, COLUMN_A, COLUMN_B FROM TABLE_A
   WHERE COLUMN_A LIKE :SearchTerm";

cmd.Parameters.AddWithValue("SearchTerm", "%" + term.Replace('%', ' ') + "%");

But I'm not happy with encasing the term with % signs - is there a correct or better way?

score 2 · Answer 1 · edited May 23 '17 at 12:20

2

Oracle itself doesn't like "Where column LIKE %:searchterm%", so if you need the wildcards in there then they need to be added as part of the parameter.

You could avoid it by setting up a full text index on the column you want to search and then use CONTAINS instead, which doesn't require the wildcards. It's also a more powerful search method, but more complex to set up.

edited May 23 '17 at 12:20

Community

1
1

answered Feb 23 '11 at 11:20

Tridus

5,021
1
19
19

The same goes for most ADO drivers. `%` should always be in the parameter. – jgauffin Feb 23 '11 at 13:11

score 2 · Accepted Answer · edited Feb 23 '12 at 06:45

2

Try this solution:

cmd.CommandText =
  "SELECT ID, COLUMN_A, COLUMN_B
   FROM TABLE_A
   WHERE COLUMN_A LIKE '%' || :SearchTerm || '%'";

cmd.Parameters.AddWithValue("SearchTerm", term);

edited Feb 23 '12 at 06:45

Andrew

12,991
15
55
85

answered Feb 23 '11 at 13:01

Devart

119,203
23
166
186

DevArt dotConnect for Oracle - how to do a WHERE LIKE clause with a parameter?

2 Answers2