Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
janetb
Yak Posting Veteran
71 Posts |
 Posted - 2006-03-28 : 17:24:28
|
| I have a need to evaluate a parameter to use like keywords. (Not my server, so indexing is not available.) I can get the first scenario to work, but wanted to see about getting the syntax so I wouldn't have to exec the statement. If I run the first execute statement, I get three rows (accurate). If I run the second statement, I get no rows. Anybody help me out with the syntax? I've tried so many variations, I'm lost. Many thanks.declare @key as varChar(50), @sql as varchar(1000) set @key='flow afow'set @sql='select myID from myTable where [title] like ''%' + replace(rtrim(ltrim(@key)),' ','%'' or [title] like ''%') + '%'''exec(@sql)select myID from myTable where [title] like '%' + replace(@key,' ', '%''' or [title] like '''%') + '%' |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-28 : 18:20:33
|
| Your first query becomes where [title] like '%flow%' or [title] like '%afow%'the secondwhere [title] like '%flow%'' or [title] like ''%afow%'i.e. the second is a single like statement.To do this without dynamic sql I think you would have to opoulate a table (or use a table valued function to parse the space delimitted string) thenjoin dbo.parsestr(@key) stron [title] like '%' + str.val + '%'and use a distinct to exclude duplicates==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2006-03-29 : 14:01:23
|
| Thanks for the reply |
|
|
|
|
|
|
Like function with replace?