C# constructing parameter query SQL - LIKE %

Question

I am trying to build SQL for a parameter query in C# for a query which will contain the LIKE %% command.

Here is what I am trying to acheive (please note that the database is Firebird)

var SQL = string.format("SELECT * FROM {0} WHERE {1} LIKE '%?%'", TABLE, NAME);
 cmd.Parameters.AddWithValue(NAME, "JOHN");

Now I have tried every single permutation to get the parameter to work, I have tried;

Adding the % character to the parameter,

cmd.Parameters.AddWithValue(NAME, "%" + "JOHN" + "%");

or

cmd.Parameters.AddWithValue(NAME, "'%" + "JOHN" + "%'");

I cannot seem to get this to work, how can I use a parameter for the LIKE query to work.

Suggestions are welcome!

it seems like there is some missing code, where are you assigning the SQL to the command? What error are you getting or is the query result just not what you expect? — Jeff Martin
– Jeff Martin, Commented Mar 19, 2009 at 22:37
yes, SQL is being assigned to the Cmd.CommandText parameter. — Myhiad
– Myhiad, Commented Mar 19, 2009 at 22:50

Guffa · Accepted Answer · 2009-03-19 22:46:01Z

28

You can't have parameters inside of a string literal in the query. Make the entire value the parameter, and add the wildcards to the string:

var SQL = string.format("SELECT * FROM {0} WHERE {1} LIKE ?", TABLE, NAME);
Cmd.Parameters.AddWithValue(NAME, "%" + "JOHN" + "%");

edited Mar 19, 2009 at 22:46

answered Mar 19, 2009 at 22:38

Guffa

703k111 gold badges760 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joel Coehoorn Over a year ago

Pay closer attention: he moved the wild cards to the parameter. This will work, but imo it's the wrong way to do it.

Guffa Over a year ago

@Myhiad: Notice that there is only ? after the like operator, no wildcard characters and no apostrophes.

Guffa Over a year ago

@Joel Coehoorn: There is no reason to be rude. The question doesn't contain any information about how the query looks after the parameter has been changed.

Joel Coehoorn · Accepted Answer · 2009-03-19 23:05:15Z

5

var SQL = string.Format("SELECT * FROM {0} WHERE {1} LIKE '%' + ? + '%'", TABLE, NAME);
Cmd.CommandText = SQL;
Cmd.Parameters.Add("?", SqlDbType.VarChar, 50).Value = "JOHN";

edited Mar 19, 2009 at 23:05

answered Mar 19, 2009 at 22:54

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

5 Comments

Myhiad Over a year ago

is this SQL valid for Firebird?

Joel Coehoorn Over a year ago

I didn't see the firebird requirement. I updated the sql to make sure it's okay, but the parameter code is wrong because the SqlDbType enum was intended for Sql Server. I still like to avoid AddWithValue, though

Myhiad Over a year ago

this is just not working for me. I am getting the following error; FirebirdSql.Data.FirebirdClient.FbException : Dynamic SQL Error expression evaluation not supported ----> FirebirdSql.Data.Common.IscException : Exception of type 'FirebirdSql.Data.Common.IscException' was thrown.

Mark Rotteveel Over a year ago

For Firebird (and standard SQL), you need to use the || concatenation operator instead of +, that is LIKE '%' || ? || '%'

David Over a year ago

I wanted to see how to do it with SQL Server, so your original answer still has value. You may consider showing both versions in your answer.

Chris · Accepted Answer · 2009-03-19 22:36:21Z

-4

In the past when doing this, i've simply integrated it into the sql, making sure that i replace single quotes with question marks to deal with sql injection. Eg:

var SQL = string.format("SELECT * FROM {0} WHERE {1} LIKE '%{2}%'",
  TABLE,
  NAME,
  JOHN.Replace("'","?"));

answered Mar 19, 2009 at 22:36

Chris

41k46 gold badges197 silver badges239 bronze badges

2 Comments

Blorgbeard Over a year ago

It would be better to replace single quotes with two single quotes, so that legitimate searches including quotes actually work. Replace("'", "''")

Ricibald Over a year ago

it exposes to sql injection attacks

Collectives™ on Stack Overflow

C# constructing parameter query SQL - LIKE %

3 Answers 3

3 Comments

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related