using parameter in SQL with LIKE keyword

Question

Hi folks,

from my C#-programm, I access a SQL Server 2008 Database. I have a table with a fulltextindex and want to search for an indexed entry:

SELECT page_id FROM page_categories WHERE page_title LIKE @title

When 'title' has no withespaces everything is fine, but when title does contain withespaces, the request fails. It throws no error, but the returned recordset is closed

command.ChangeParameter("title", title);

using (System.Data.IDataReader reader = command.ExecuteQuery())
{
  while (reader.Read())
    resultSet.Add(reader.GetInt32(0));

  reader.Close();
}

I cannot enclose the @title parameter with '', because then the Command-Object would interpret the @title as a string and not a parameter.

SELECT page_id FROM page_categories WHERE page_title LIKE '@title'

Anyone knows how I can use a parameter for the LIKE request when the title-parameter can contain whitespace?

Thanks in advance, Frank

Answer 1

You can do this:

SELECT page_id 
FROM page_categories 
WHERE page_title LIKE '%' + @title + '%'

Answer 2

alternatively to the ck's answer, you could do this:

command.ChangeParameter("title", "%"+title.Trim()+"%");

Answer 3

You don't use LIKE to search a freetext index. You use CONTAINS or FREETEXT or similar.

Answer 4

SELECT page_id 
FROM page_categories 
WHERE page_title LIKE '%' + @title + '%'

The % represents a wild char similar to the more common * outside SQL (Any string of zero or more characters).

You should make sure that in the calling code the parameter is trimmed via .Trim().

Here is the full reference of possible wildchars.

Answer 5

I add the single quotes to the query:

SELECT page_id FROM page_categories WHERE page_title LIKE '' + @title + ''

If I try this with 'Contains' ( CONTAINS (page_title, '' + @tile + '') ), I get a syntax error.