CODEWITHSUNDEEP
sql-servercase-insensitiveaccent-insensitive
Marco Marsala
Marco Marsala

Reputation: 2462

MSSQL space insensitive search

How to make a space insensitive search in SQL Server?

For example those city names should be considered equal:

San Remo
Sanremo

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

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

Upvotes: 0

Views: 168

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44326

Create a new computed PERSISTED column. This will allow creation of index. Even without an index, this will perform much better than the replace directly on the column:

ALTER TABLE dbo.YourTable 
ADD ComputedCol AS REPLACE(YourColumn, ' ', '') PERSISTED

Upvotes: 2

Alex Tartan
Alex Tartan

Reputation: 6836

You could search on 

REPLACE (column_name, ' ', '') like '%yourCriteria%

rather than just

column_name like '%yourCriteria%

I would advice against it, since text searches are more resource consuming. A better option is to add a new column containing the replaced text, index it, and search on that.

Upvotes: 0

Related Questions