February 18, 2016 at 4:31 am
Hi,
I'm trying to implement a text search in a table's data...
What I did was create a table (idRecord, word) with all the words of a record.
I also have a function that brakes a string into to search words...
With the following code I do a search:
DECLARE @wordsToLookUp TABLE(item VARCHAR(255))
DECLARE @wordCount INT
INSERT INTO @wordsToLookUp (item) SELECT item FROM dbo.Iterativewordchop('med imov', 0)
SELECT @WordCount = COUNT(1) FROM @wordsToLookUp
SELECT @wordCount
SELECT e.[id] FROM entities e WHERE EXISTS (SELECT 1 FROM entitiesFullText CW INNER JOIN @wordsToLookUp WLU ON CW.word LIKE '%' + WLU.item + '%' WHERE e.[id] = CW.[id] GROUP BY [id] HAVING COUNT(1) >= @wordCount)
Apparently this works fine.... But there's a catch...
If I search for 'med imov' and a record has meda and medb and not imov the record is returned....
Is there an "easy" and fast way to search each word in @wordsToLookUp individually?
Thanks,
Pedro
February 18, 2016 at 6:22 am
I have this old snippet form a post from years ago.
both this and your example are non-sargable, meaning it's going to table scan for the values, which is what full text indexing is designed to resolve.
here's an example.
it builds the WHERE statement to use as an example; maybe this will help?
WHERE CHARINDEX('civil',YOURTABLE.COLUMNNAME) > 0
AND CHARINDEX('war',YOURTABLE.COLUMNNAME) > 0
AND CHARINDEX('memorabilia',YOURTABLE.COLUMNNAME) > 0
DECLARE @SEARCHSTRING VARCHAR(8000),
@COLUMNNAME VARCHAR(128),
@vbCrLf CHAR(2)
SET @SEARCHSTRING='civil war memorabilia'
SET @COLUMNNAME = 'YOURTABLE.COLUMNNAME'
SET @vbCrLf = CHAR(13) + CHAR(10)
SET @SEARCHSTRING = 'WHERE CHARINDEX(''' + REPLACE(@SEARCHSTRING,' ',''',' + @COLUMNNAME + ') > 0 ' + @vbCrLf + ' AND CHARINDEX(''') + ''',' + @COLUMNNAME + ') > 0' + @vbCrLf
SELECT @SEARCHSTRING
Lowell
February 18, 2016 at 7:28 am
Hi,
I suggest you construct some dynamic sql as per below, using the intersect operator instead of the AND.
select id from entitiesFullText eft where eft.word like 'med%'
INTERSECT
select id from entitiesFullText eft where eft.word like 'imov%'
Also best to limit the search, if you can, to 'words that begin with' rather than 'words that contain'. In that way you should be able to use indexes etc..
If I remember well, like 'abc%' can use an index whereas like '%abc%' clearly cannot....and if per chance like 'abc%' doesn't use an index then use charindex instead of like - as I'm sure it can.
But the real 'trick' here is the use of the intersect!
Regards,
David McKinney.
February 18, 2016 at 8:04 am
Lowell (2/18/2016)
I have this old snippet form a post from years ago.both this and your example are non-sargable, meaning it's going to table scan for the values, which is what full text indexing is designed to resolve.
here's an example.
it builds the WHERE statement to use as an example; maybe this will help?
WHERE CHARINDEX('civil',YOURTABLE.COLUMNNAME) > 0
AND CHARINDEX('war',YOURTABLE.COLUMNNAME) > 0
AND CHARINDEX('memorabilia',YOURTABLE.COLUMNNAME) > 0
DECLARE @SEARCHSTRING VARCHAR(8000),
@COLUMNNAME VARCHAR(128),
@vbCrLf CHAR(2)
SET @SEARCHSTRING='civil war memorabilia'
SET @COLUMNNAME = 'YOURTABLE.COLUMNNAME'
SET @vbCrLf = CHAR(13) + CHAR(10)
SET @SEARCHSTRING = 'WHERE CHARINDEX(''' + REPLACE(@SEARCHSTRING,' ',''',' + @COLUMNNAME + ') > 0 ' + @vbCrLf + ' AND CHARINDEX(''') + ''',' + @COLUMNNAME + ') > 0' + @vbCrLf
SELECT @SEARCHSTRING
It works fine but I need the query to be in a table value function so I can join with another query...
The filter can be just by text or by text and other fields and I need a TVF to return the ids and join with another query....
The app has a "just text" search and a search with fields and text....
I can create just one search procedure and ignore the others fields (dynamic SQL...). It's be best choice, right?!
Thanks,
Pedro
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply