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