Function to remove accent in string

[Darren Jensen]

Hi,

Does anyone have a function which replaces accent chars from a string
with the non-accented equivalent? For example 'hôpital' should return
'hopital'.

Thank you in advance.

[David Portas]

Is this so that you can compare differently accented strings? If so and if
you are using SQL2000 then there is no need actually to replace the accented
characters. Just use an accent-insensitive collation for your comparisons:

IF 'hôpital'='hopi tal' COLLATE Latin1_General_ CI_AI
PRINT 'YES'

This avoids an expensive update. Better still, if you can change the column
collation to be accent-insensitive then you can create an index on the
column to help with the comparison.

Otherwise you could replace the accented chars like this:

UPDATE YourTable SET col =
REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(REPLA CE(
REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(REPLA CE(
REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(REPLA CE(
REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(REPLA CE(
REPLACE(REPLACE (col COLLATE Latin1_General_ CI_AI
,'a','a'),'b',' b'),'c','c'),'d ','d'),'e','e') ,'f','f')
,'g','g'),'h',' h'),'i','i'),'j ','j'),'k','k') ,'l','l')
,'m','m'),'n',' n'),'o','o'),'p ','p'),'q','q') ,'r','r')
,'s','s'),'t',' t'),'u','u'),'v ','v'),'w','w') ,'x','x')
,'y','y'),'z',' z')

Again, this assumes you are using 2000. If correct case is important to you
then specify a case-sensitive collation in place of Latin1_General_ CI_AI and
add nested REPLACE statements for all the upper-case letters too.

--
David Portas
SQL Server MVP
--

[Darren Jensen]

Thanks David.

I tried changeing the column to be accent-insensitive as you suggest
and indeed it does work when using a simple SQL statement with a WHERE
clause (WHERE 'hôpital'='hopi tal'). However, I am using freetext
search on this column and when I use CONTAINSTABLE there is no result
found for 'hopital' it only works for 'hôpital'. Do you have any ideas
how I can get this to work without using a replace function?

Darren.

"David Portas" <RE************ *************** *@acm.org> wrote in message news:<pr******* *************@g iganews.com>...

Is this so that you can compare differently accented strings? If so and if
you are using SQL2000 then there is no need actually to replace the accented
characters. Just use an accent-insensitive collation for your comparisons:

IF 'hôpital'='hopi tal' COLLATE Latin1_General_ CI_AI
PRINT 'YES'

This avoids an expensive update. Better still, if you can change the column
collation to be accent-insensitive then you can create an index on the
column to help with the comparison.

Otherwise you could replace the accented chars like this:

UPDATE YourTable SET col =
REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(REPLA CE(
REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(REPLA CE(
REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(REPLA CE(
REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(REPLA CE(
REPLACE(REPLACE (col COLLATE Latin1_General_ CI_AI
,'a','a'),'b',' b'),'c','c'),'d ','d'),'e','e') ,'f','f')
,'g','g'),'h',' h'),'i','i'),'j ','j'),'k','k') ,'l','l')
,'m','m'),'n',' n'),'o','o'),'p ','p'),'q','q') ,'r','r')
,'s','s'),'t',' t'),'u','u'),'v ','v'),'w','w') ,'x','x')
,'y','y'),'z',' z')

Again, this assumes you are using 2000. If correct case is important to you
then specify a case-sensitive collation in place of Latin1_General_ CI_AI and
add nested REPLACE statements for all the upper-case letters too.

[David Portas]

I'm not an expert with Full Text but see this thread:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

--
David Portas
SQL Server MVP
--