using parameter in SQL with LIKE keyword
from my C#-programm, I access a SQL Server 2008 Database. I开发者_Python百科 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
You can do this:
SELECT page_id
FROM page_categories
WHERE page_title LIKE '%' + @title + '%'
alternatively to the ck's answer, you could do this:
command.ChangeParameter("title", "%"+title.Trim()+"%");
You don't use LIKE to search a freetext index. You use CONTAINS or FREETEXT or similar.
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.
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.
精彩评论