SQL Server Performance Forum – Threads Archive
Search for some word
I want to do a search in some field and I want to pass a parameter that contain 1 or some word. Is it possible to obtain the results that contains those words but not necessary in the order. I did a ‘like’ but we return only the row that contains the string we send. For the moment I have this store procedure but I know this is bad:CREATE PROCEDURE sp_News_Get
@SearchText varchar(150)
AS
SELECT * FROM tblNews
WHERE Title like ‘%@SearchText%’ OR Description like ‘%@SearchText%’ OR Header like ‘%@SearchText%’ OR Credit like ‘%@SearchText%’
GO If I wanna search this string: ‘congo president’
I wanna receive all row that contain in their text congo and president:
I have congo on Description and president on Credit it’s good.
I have congo on Description and president on Description it’s good.
I have congo on Description and I did’nt found president it’s bad. Is it enough clear? Sorry for my bad english. Thank you Pascal
select *
from tblNews
where Title + Description + Header + Credit like ‘%’ + replace(@SearchText, ‘ ‘, ‘%’) + ‘%’
and <additional criteria>
That would work, but it would be slow unless you add additional criteria like date range.
Actually the problem with the solution above is the word order. I’ll post better solution later (I don’t have time for it right now). The idea is to split words into table variable (using table udf) and use then join with that table function.
I modified your stored procedure and came up with the script below. This will work for your current scenario and should output what you are looking for. It may need modification for other scenarios. I preferred to use Mirko’s code for performing the comparison, since it was neater than yours. Also, this particular script performs a full table scan, so it is very inefficient, and as was suggested, add additional criteria or it can be very slow. CREATE PROCEDURE sp_News_Get
@SearchText varchar(150) AS DECLARE @string1 varchar(100)
DECLARE @string2 varchar(100) SET @SearchText = LTRIM(RTRIM(@SearchText)) IF CHARINDEX(‘ ‘, @SearchText) <> 0
BEGIN
SET @string1 = SUBSTRING(@SearchText,1,CHARINDEX(‘ ‘,@SearchText,1)-1)
SET @string2 = SUBSTRING(@SearchText,CHARINDEX(‘ ‘,@SearchText,1)+1,LEN(@SearchText)) SELECT *
FROM tblNews
WHERE Title + Description + Header + Credit
LIKE (‘%’ + REPLACE(@string1, ‘ ‘, ‘%’) + ‘%’)
OR Title + Description + Header + Credit
LIKE (‘%’ + REPLACE(@string2, ‘ ‘, ‘%’) + ‘%’) END ELSE SELECT *
FROM tblNews
WHERE Title + Description + Header + Credit
LIKE (‘%’ + REPLACE(@SearchText, ‘ ‘, ‘%’) + ‘%’)
go
– Tahsin
Here it is, dynamic sql solution. Join solution would be more complex and far less efficient, because I would have to use group by.
CREATE PROCEDURE sp_News_Get
@SearchText varchar(150)
AS
declare @query varchar(4000)
set @query = ‘select * from tblNews where Title + Description + Header + Credit like ”%’
set @query = @query + replace(@searchText, ‘ ‘, ‘%” and Title + Description + Header + Credit like ”%’) + ‘%”’
exec(@query)
With small changes you can make solution using sp_executeSQL. This solution is general, because you can have any number of words fitting in 150 characters, separated by space. You can have just one space between two words and you can have it at the beginning or at the end of string. You still have to include more sargable restrictions to make it efficient. I think, the best additional criteria could be
newsDate between @start and @end
]]>