Using LIKE in selects

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello.
I'm having trouble using the SQL LIKE keyword in the WHERE clause.
Specifically I have trouble using parameters like so (running against SQL
Server):
SqlCommand cmd = new SqlCommand(
"SELECT * FROM Users WHERE first_name LIKE @first_name AND last_name
LIKE @last_name", conn);
SqlParameter param = new SqlParameter();
param.ParameterName = "@first_name";
param.Value = "Jo%";
cmd.Parameters.Add(param);
SqlParameter param = new SqlParameter();
param.ParameterName = "@last_name";
param.Value = "Sm%";
cmd.Parameters.Add(param);

The query doesn't return anything, but that table contains a row with Joe
Smith, which does turn up when I run the query in the Query Analyzer. I've
done w workaround to do text substitution for now, but that's not the correct
way to handle this. Any ideas? Thanks
 
You might try running SQL Profiler to see exactly what syntax is being sent
to the server.

Jeff
 
If this is C#, then the % character may be treated like an escape character.
Profiler would catch this

Jeff
 
Thank you! That solved my problem. Turned out I was getting an exception that
I missed catching. See the actual query going out was the trick.

Snorlax
 
The SQL ends up wrong the way you are doing it.

The correct query ends up more like:
"SELECT * form Users where First_name LIKE '%" + firstNameValue + "'"

When you do a LIKE with a literal and no wildcard, you are performing an
exact match, evne though you are using the like word.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************
 
Hi Jeff,

Jeff Dillon said:
If this is C#, then the % character may be treated like an escape
character.
Click to expand...

Nope. Only chars prefixed with \ are treated like special chars (when
strings are not prefixed with @)
 
Back
Top