Actually there is a way to wildcard full text search using both prefixes and suffixes combined with NEAR
operator in SQL Server.
This is a little bit more expensive since it requires some preprocessing of the query. You can selecting the needed individual words from the full text index views sys.dm_fts_index_keywords
or sys.dm_fts_index_keywords_by_document
. After you have all the words you will need to use a CROSS JOIN
to create the query.
Here is some sample code to show how it works:
CREATE TABLE dbo.TBL_NAME
(ID INT NOT NULL,
field_name NVARCHAR(1000));
CREATE UNIQUE INDEX ui_id ON dbo.TBL_NAME(ID);
CREATE FULLTEXT CATALOG ft WITH ACCENT_SENSITIVITY=OFF AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.TBL_NAME(field_name)
KEY INDEX ui_id WITH (STOPLIST = OFF); -- Or SYSTEM, you choose
GO
INSERT INTO dbo.TBL_NAME VALUES(1,'The Ministry of Education is open.');
INSERT INTO dbo.TBL_NAME VALUES(2,'The ministerial activity of many departments result ' +
'in several educators determining to retire.');
INSERT INTO dbo.TBL_NAME VALUES(3,'The ministerial activity of many departments result ' +
'in several exacting processes that are intended to prevent extra work by the educators.');
WAITFOR DELAY '00:00:05'; -- Give IFTS a few seconds to index.
-- Tables Created for resolving Wildcard names
DECLARE @FirstGroup TABLE (AGroup NVARCHAR(50));
DECLARE @SecondGroup TABLE (BGroup NVARCHAR(50));
INSERT INTO @FirstGroup
SELECT display_term
FROM sys.dm_fts_index_keywords (db_id('TEST'),object_id('TBL_NAME'))
WHERE display_term like '%minist%';
INSERT INTO @SecondGroup
SELECT display_term
FROM sys.dm_fts_index_keywords (db_id('TEST'),object_id('TBL_NAME'))
WHERE display_term like 'educ%';
-- Declare a search string that will OR the words that match your wildcards
DECLARE @SearchString NVARCHAR(2000);
SET @SearchString = ''
SELECT @SearchString = @SearchString + 'NEAR(('+ AGroup +','+BGroup+'),10, TRUE) OR '
FROM @FirstGroup CROSS JOIN @SecondGroup
SELECT @SearchString = LEFT(@SearchString, LEN(@SearchString) - 3); -- Trim the last OR
-- This will only select the first two rows, since the last row exceeds
-- the distance limit
SELECT * FROM dbo.TBL_NAME
WHERE CONTAINS(field_name,@SearchString)
GO
DROP FULLTEXT INDEX on dbo.TBL_NAME;
DROP FULLTEXT CATALOG ft;
DROP TABLE dbo.TBL_NAME;
GO
Since SQL Server 2008 this has been supported by the two views mentioned above. If the overhead of collecting the needed words is not too expensive, it does open up new possibilities for Full Text searching.
<prefix_term>
in the linked page