sql-serveraccent-insensitive

MSSQL quote insensitive search

How to make a quote insensitive search in SQL Server?

For example those city names should be considered equal:

Muggio'
Muggio

I know how to make a simple accent-insensitive search but I found nothing about quote-insensitive searches.

There is a collation or a tidy function I can use? Or I should preprocess the city name in my code?

Solution 
  • DECLARE @t TABLE(v VARCHAR(20))
INSERT INTO @t VALUES
('abc'''),
('abc')

DECLARE @f VARCHAR(20) = 'abc'''
SELECT * FROM @t WHERE v LIKE '%' + REPLACE(@f, '''', '') + '%'

    Output:

    abc'
abc