0

I'm trying to do a search of all the columns of a specific table and I want to return the result that contains certain characters. For example

Entered Search Value: "Josh"

Output Values: Josh, Joshua, Joshie, Rich Joshua

I want to return all values containing the characters Josh. I'm trying to use FreeTextTable however it only returns exact words like this

Entered Search Value: "Josh" Output Values: Josh

I'm using the following code.

DECLARE @nameSearch NVARCHAR(100) = 'Josh';

SELECT MAX(KEY_TBL.RANK) as RANK, FT_TBL.ID
FROM Property FT_TBL 
INNER JOIN (SELECT Rank, [KEY]
            from FREETEXTTABLE(Property, *, @nameSearch)) AS KEY_TBL
ON FT_TBL.ID = KEY_TBL.[KEY]
GROUP BY FT_TBL.ID

I know this will be possible by using LIKE or CONTAINS but I have a lot of rows in that table and it would take time before it returns the result. So I need to use FreeTextTable to get the Rank and Key. However I can't achieve my goal here. I need help. Thanks!

Abra
  • 19,142
  • 7
  • 29
  • 41
Rich
  • 3,928
  • 4
  • 37
  • 66
  • Append " on both sides of @locationSearch and try to run the query From this link: https://www.toadworld.com/platforms/sql-server/w/wiki/9702.fts-freetext-freetexttable --> Note: If you wrap your FreeText or FreeTextTable search phrase in double quotes (ie “My Searchâ€), the stemming and thesaurus features will be disabled and a Contains (or literal) search will be performed. – Sujith Aug 11 '17 at 03:57
  • @Sujith nah, it doesn't work. – Rich Aug 11 '17 at 05:11

0 Answers0