Searching by ignoring special characters

Wim · #1 (**permalink**) 12-14-11, 08:54

Hi,

In our schools we have a number of East-European, Turkish, Scandinavian, ... students. Their names contain "special" characters, like Ö, Ü, Ø, ... Our users want to be able to search for student names without having to enter those special characters. Most often they don't know the exact spelling of the names and they get "no match found" messages as a result.

They want to have persons with the name Ösgür, Osgueld, ... in the result set after entering "osgu" in the search field.

What is the best way to do this?
I was thinking about using another collation near the LIKE, but I don't know if that would work and how it should be done. The Database collation is Latin1_General_CI_AS.

weejas · #2 (**permalink**) 12-14-11, 10:40

Bloody users and their requirements...

Perhaps a customised Soundex function would help here? It might be a hassle to write, but it would then be easy to update in the event of new characters appearing. I have one in VBA that could be amended and translated in T-SQL, probably.

Pat Phelan · #3 (**permalink**) 12-14-11, 12:39

Code:

DECLARE @c			NVARCHAR(99)

SET @c = 'Ösgür'

SELECT
   CASE WHEN @c LIKE N'Osg%'
      THEN 'Yes'
      ELSE 'No'
   END AS Naked
,  CASE WHEN @c COLLATE Latin1_General_CI_AI LIKE N'Osg%'
      THEN 'Yes'
      ELSE 'No'
   END AS Fancy

-PatP

Wim · #4 (**permalink**) 12-14-11, 15:10

Pat,

Thanks a lot!

Latin1_General_CI_AS
- CI stands for Case Insensitive
- AS for Accent Sensitive

Latin1_General_CI_AI
- AI stands for Accent Insensitive. For me an accent is something like é or è, I didn't realise it also includes Ö, Ü and the likes.

Wim · #5 (**permalink**) 12-14-11, 15:12

Quote:

Originally Posted by weejas

Bloody users and their requirements...

I could see their point in this case.

Internet Services

Web Development

Digital Marketing

Consumer Tech