sql search on fields containing diacritics

Question

Hi guys,

I'm working at a web site search which uses Sql server 2008 express edition.

I have 'Books' table which has a field named 'Title' where there are titles containing romanian (latin) characters.

Well, if the user inserts a word like: 'casa' in the search field and i have a title in db like: 'test casă test' i want to show that book but:

select * from books where title like '%casa%'

will not find it...

Do you know any function which removes the diacritics when doing the select?

I know that i can solve the problem with full text searching add-on of sql server but i want to do it in a simpler way.

Thanks.

Answer 1

select * from books where title COLLATE Latin1_General_CI_AI like '%casa%'

Of course, you should choose a collation that matches yours... just change AS to AI on the end to make it "accent insensitive"

Quick example with German umlauts

DECLARE @foo TABLE (bar varchar(100) /*implied COLLATE Latin1_General_CI_AS*/)

INSERT @foo VALUES ('xxx fish yyy')
INSERT @foo VALUES ('xxx bar yyy' )
INSERT @foo VALUES ('xxx bär yyy')

select * from @foo where bar COLLATE Latin1_General_CI_AI like '%bar%'
select * from @foo where bar like '%bar%'

Answer 2

You need to use an accent insensitive collate clause.