Search code examples
sqlt-sqlfull-text-search

SQL Server : search inside single quote

I have Table1 in SQL Server like this:

ID | Name
---+--------------
1  | Robert Bart
2  | Andy Wor
3  | Sam O'Neil
4  | Bill Neil

Full text is enabled and my T-SQL looks like this:

SELECT *
FROM Table1
WHERE CONTAINS([Name], @Keywords)

The parameter @Keywords is of type NVARCHAR(MAX).

My problem is, when @Keyword is Neil I only get record #4 as result; is there a way to get the records #3 and #4 from a select passing Neil as @Keywords?

I'm using MS SQL Server 2017.

Solution

  • You will have a hard time to customize Microsoft's FTS.
    Word breakers change with every version of SQL Server and depend on the language you used to create the fulltext index.
    You might be able to find a default language that splits O'Neil to O + Neil, but i wouldn't count on that.
    More info on setting locale identifiers: CREATE FULLTEXT INDEX with a LCID

    If you really want to customize the word breakers, you have 2 options:

    1. Create a custom dictionary
    2. Write a Language Resource DLL

    Easiest workaround would be to split the names by delimiters, like quotes, before inserting them to Table1 like REPLACE(Name, '''', ' ')
    If you need the original full name, just store that in a different column/table.