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'.
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
--
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. [quoted text, click to view] "David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:<pr2dnfJO_6QZCTzcRVn-tw@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
Don't see what you're looking for? Try a search.
|
