CONTAINS operator using '@'

Question

Hey guys,

I am trying to use the Contains operator. It works fine with test data eg.
WHERE CONTAINS(file,'"*ash*"')

However, I want to get the keyword from a TextBox using something like CONTAINS(file,'"*@key*"'), but this doesnt seem to work. Any suggestions please.

Thanks

Answer 1

The @ is used to declare a variable in SQL. For this reason, it doesn't need to be inside quotes. For example:

DECLARE @Key varchar(100);
SET @Key = '*ash*'; 
SELECT file FROM SomeTable WHERE CONTAINS(file, @Key);

EDIT: If you are passing inline SQL through your code then, I would suggest something like this:

C# Example:

string key = textBox1.Text;
string query = "SELECT file FROM SomeTable WHERE ";
query += String.Format("CONTAINS(file, '\"*{0}*\"')", key);