CODEWITHSUNDEEP
sqlsql-server-2008collation
Cristian Boariu
Cristian Boariu

Reputation: 9621

sql search on fields containing diacritics

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.

Upvotes: 16

Views: 9427

Answers (3)

Petr Tomicek
Petr Tomicek

Reputation: 21

If you need to do it in linq, I think you have to remove diacritics first in the searched string and then to make a linq query... it should work...

Upvotes: 0

gbn
gbn

Reputation: 432261

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%'

Upvotes: 22

Martin Smith
Martin Smith

Reputation: 453278

You need to use an accent insensitive collate clause.

Upvotes: 6

Related Questions