October 21, 2010 at 12:29 pm
Hello everyone,
Sorry if this is somewhere, I just could not find.
Today, I have a internal app where users can do a search for documents. The document name field is just a varchar(255) field.
When the procedure gets called, it receives a @name parameter, and inside it, it executes a LIKE statement, in the following form:
LIKE '%' + REPLACE(@name, ' ', '%') + '%'
This has been running for some time now, but has problems as keywords must be put in the order they are in the document title. My question is: can I achieve this using a table parameter that receives a list of words being looked for, I meam, find all documents which name has the words inside the table param?
Thanks in advance,
Marcelo
October 21, 2010 at 1:10 pm
Found this, which might be useful to you:
Pattern matchingMatches patterns of characters
-- Author Gianluca Sartori
--http://www.sqlservercentral.com/Forums/Topic1008514-391-1.aspx
-- Table to hold the words to search for
DECLARE @Patterns TABLE (
search_pattern varchar(50)
)
-- Table to hold the text to search in
DECLARE @Test TABLE (
test_text nvarchar(max)
)
-- Populate the test table with the text of the
-- stored procedures in database msdb
INSERT INTO @Test
SELECT definition
FROM msdb.sys.sql_modules
-- Define some patterns to search for
--INSERT INTO @Patterns VALUES ('sysmail')
--INSERT INTO @Patterns VALUES ('jobserver')
INSERT INTO @Patterns VALUES ('Raiserror')
-- Select out the stored procedures that match any of the patterns
SELECT *
FROM @Test AS A
WHERE EXISTS (
SELECT 1
FROM @Patterns AS B
WHERE A.test_text LIKE '%' + B.search_pattern + '%'
)
I would advise your users NOT to input common words for example, searching for a title of "The Best of All Worlds", I suggest that you instruct them to only input "Best",and "Worlds", so as to reduce the number of items returned.
October 22, 2010 at 3:22 am
Hi Marcelo,
You might want to look up "Full Text Index" in BOL. That should be perfect for what you're trying to do.
Overview:
http://msdn.microsoft.com/en-us/library/ms142571.aspx
Searching multiple values:
http://msdn.microsoft.com/en-us/library/ms142583.aspx#Using_Boolean_Operators
Regards, Iain
October 22, 2010 at 6:17 am
@Ron,
Thanks a lot. It has really been useful. The only thing that I'm trying to achieve now is relevance, since it finds everything that has *any* of the words, I'd like to rank for *all* - *most* - *any* of them. If you have any hint on that, I'd be thankful.
@Iain,
Thanks! I really thought about Full Text Index. A shame my host doesn't provide access to it :ermm:
October 22, 2010 at 6:17 am
Sorry, double posted. Don't know why...
October 22, 2010 at 9:47 am
I passed your thanks onto the Author Gianluca Sartori (as noted in the code posted) and requested that he look at this forum and just maybe he can come up with a set based solution. All I can come up with is a $#%cursor solution and that would make the process run so slowly as to be painful.
October 22, 2010 at 9:53 am
Change the query like this:
SELECT *
FROM @Test AS A
CROSS APPLY (
SELECT COUNT(*) AS matched_patterns
FROM @Patterns AS B
WHERE A.test_text LIKE '%' + B.search_pattern + '%'
) AS B
WHERE matched_patterns > 0
ORDER BY matched_patterns DESC
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply