CODEWITHSUNDEEP
sqlsql-server
JLo
JLo

Reputation: 3157

SQL Text comparison - dealing with multiple apostrophe types

I am attempting to write some code to search a varchar field and match results based on a partial/complete input match in SQL Server (2008+)

The column we are searching is an NVARCHAR column so supports many kinds of apostrophes and quotation marks. These include, but are probably not limited to:

Likewise there are various double-quotation marks which need to be considered...

The user could enter any of the single quotation marks and I want to return results which contain any of the variations of that single character, and the same for the double-quotation marks.

Much like a _CI collation would return results for "e" or "E" when searching for "e", and an _AI collation would return results for à or ä when searching for "a", is there a collation which will ignore variations of quotation marks?

The current workaround is to do:

WHERE my_field LIKE REPLACE( REPLACE( REPLACE( REPLACE( search_term, CHAR(145), CHAR(39)), CHAR(146), CHAR(39)), CHAR(147), CHAR(34)), CHAR(148), CHAR(34))

(Which is not scalable and horrible to read)

Upvotes: 1

Views: 605

Answers (1)

Hogan
Hogan

Reputation: 70538

A standard practice is to create a "normalized" version of the searchable data with plurals removed and punctuation normalized or removed.

SQL Server provides this and additional search features with their "Full Text Search" feature. To use FTS you need to set it up and enable it, it is not enabled by default.

You can also roll your own if you don't need all the features of FTS. Depending on your requirements this may or may not be worth it.

Upvotes: 1

Related Questions