SQL LIKE statement with querystring

[buggiez]

hi all.

for my gridview, i have a datasource and it includes an sql statement. i would like my sql statement to retrieve records by passing a query string.


this is my sql statement:

SELECT * FROM [mailer]
where subject LIKE '% '@s' %'


obviously i would like to retrieve the records based on the querystring, and if the records consist of that query string, it will be retrieved out.

however i got errors like: Incorrect syntax near '@s'.

i tried changing the statement to

where subject LIKE '% @s %'
where subject LIKE '% [@s] %'

but still it doesnt work.

please advise me. thanks in advance

 

[cjard]

I'm assuming that @s is a parameter, and not you literally searching for any field that contains @s as a string?

SELECT * FROM [mailer] WHERE subject LIKE @s

Then put the % into your parameter:

cmd.Parameters("@s").Value = "%smith%"

 

[TechGnome]

Alternative:

SELECT * FROM [mailer] WHERE subject LIKE ('%' + @S + '%')

I've successfully used this construct.

-tg

 

[buggiez]

thanks alot it works ^^

 

[cjard]

Alternative:

SELECT * FROM [mailer] WHERE subject LIKE ('%' + @S + '%')

I've successfully used this construct.

-tg

It does, however, limit this search to always performing that wildcard. Taking control of that in code meand you can re-use the search for specific items, and:

LIKE 'smith'

is as fast as = 'smith'

whereas any field starting with % cannot easily be used with an index..