Using LIKE in selects

[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

 

[Jeff Dillon]

You might try running SQL Profiler to see exactly what syntax is being sent
to the server.

Jeff

 

[Jeff Dillon]

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

Jeff

 

[Guest]

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

 

[Cowboy \(Gregory A. Beamer\)]

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!
*************************************************

 

[Miha Markic [MVP C#]]

Hi Jeff,

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

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