468,275 Members | 1,859 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,275 developers. It's quick & easy.

Function to remove accent in string

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.
Jul 20 '05 #1
3 20439
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'='hopital' 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(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
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
--
Jul 20 '05 #2
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'='hopital'). 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********************@giganews.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'='hopital' 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(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
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.

Jul 20 '05 #3
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
--
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by JB | last post: by
1 post views Thread by Fabrice | last post: by
2 posts views Thread by Ramon | last post: by
7 posts views Thread by John Devlon | last post: by
4 posts views Thread by MC | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
1 post views Thread by MrBee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.