How to use the LIKE operator in a C# Command?

Question

I need to insert a string into an Sql Command

search.CommandText = "SELECT * FROM Contacts WHERE Name like ' + @person + % + '";

What it the right way of using LIKE in a command?

Answer 1

Should be:

SELECT * FROM Contacts WHERE Name like @person + '%'

@person is a parameter - you don't need single quotes around it. You only need to concatenate it with %, which should have quotes.

Keep in mind:

• You could have kept it "SELECT * FROM Contacts WHERE Name like @person", and have the parameter value contain % (concatenate in C# is simpler to understand).
• You may also want to escape other wildcard characters already in the string: %, _, [ and ].

Answer 2

Use Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

The "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

For Example:

• LIKE '%xy' would get you anything ending with 'xy'
• LIKE '%xy%' would get you anything contains the 'xy'
• LIKE 'xy%' would get you anything starting with 'xy'