CODEWITHSUNDEEP
sqlsql-serversearchtext
Serg88
Serg88

Reputation: 41

SQL Server full text search and spaces

I have a column with a product names. Some names look like ‘ab-cd’ ‘ab cd’ Is it possible to use full text search to get these names when user types ‘abc’ (without spaces) ? The like operator is working for me, but I’d like to know if it’s possible to use full text search.

Upvotes: 2

Views: 5060

Answers (3)

gofr1
gofr1

Reputation: 15977

No, unfortunately you cannot make such search via full-text. You can only use LIKE in that case LIKE ('ab%c%')

EDIT1:

You can create a view (WITH SCHEMABINDING!) with some id and column name in which you want to search:

CREATE VIEW dbo.ftview WITH SCHEMABINDING 
AS
SELECT  id,
        REPLACE(columnname,' ','') as search_string
FROM YourTable

Then create index

CREATE UNIQUE CLUSTERED INDEX UCI_ftview ON dbo.ftview (id ASC)

Then create full-text search index on search_string field.

After that you can run CONTAINS query with "abc*" search and it will find what you need.

EDIT2:

But it wont help if search_string does not start with your search term.

For example:

ab c d -> abcd and you search cd

Upvotes: 2

Jodrell
Jodrell

Reputation: 35706

If you want to use FTS to find terms that are adjacent to each other, like words separated by a space you should use a proximity term.

You can define a proximity term by using the NEAR keyword or the ~ operator in the search expression, as documented here.

So if you want to find ab followed immediately by cd you could use the expression,

'NEAR((ab,cd), 0)'

searching for the word ab followed by the word cd with 0 terms in-between.

Upvotes: 3

TomTom
TomTom

Reputation: 62093

No. Full Text Search is based on WORDS and Phrases. It does not store the original text. In fact, depending on configuration it will not even store all words - there are so called stop words that never go into the index. Example: in english the word "in" is not selective enough to be considered worth storing.

Some names look like ‘ab-cd’ ‘ab cd’

Those likely do not get stored at all. At least the 2nd example is actually 2 extremely short words - quite likely they get totally ignored.

So, no - full text search is not suitable for this.

Upvotes: 1

Related Questions