C# constructing parameter query SQL - LIKE %
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!
Solution 1:
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" + "%");
Solution 2:
var SQL = string.Format("SELECT * FROM {0} WHERE {1} LIKE '%' + ? + '%'", TABLE, NAME);
Cmd.CommandText = SQL;
Cmd.Parameters.Add("?", SqlDbType.VarChar, 50).Value = "JOHN";