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 'hpital' should return
'hopital'.

Thank you in advance.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 'hpital'='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 'hpital'='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 'hpital'. Do you have any ideas
how I can get this to work without using a replace function?

Darren.

"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 'hpital'='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.|||I'm not an expert with Full Text but see this thread:
http://www.google.com/groups?hl=en&...FTNGP12.phx.gbl

--
David Portas
SQL Server MVP
--

Function that replaces ntext and compares ntext with nvarchar

I am running this query to an sql server 2000 database from my asp
code:
"select * from MyTable where
MySqlServerRemoveStressFunction(MyNtextColumn) = '" &
MyAdoRemoveStressFunction(MyString) & "'"

The problem is that the replace function doesn't work with the ntext
datatype (so as to replace the stresses with an empty string). I had
to implement the MySqlServerRemoveStressFunction, i.e. a function that
takes a column name as a parameter and returns the text contained in
this column having replaced some letters of the text (the letters with
stress). Unfortunately, I could not do that because user-defined
functions cannot return a value of ntext.

So I have the following idea:
"select * from MyTable where
CheckIfTheyAreEqualIngoringTheStesses(MyNtextColum n, '" & MyString &
"')"

How can I implement the CheckIfTheyAreEqualIngoringTheStesses
function? (I don't know how to combine these functions to do what I
want: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT)(verb13@.hotmail.com) writes:

Quote:

Originally Posted by

I am running this query to an sql server 2000 database from my asp
code:
"select * from MyTable where
MySqlServerRemoveStressFunction(MyNtextColumn) = '" &
MyAdoRemoveStressFunction(MyString) & "'"
>
The problem is that the replace function doesn't work with the ntext
datatype (so as to replace the stresses with an empty string). I had
to implement the MySqlServerRemoveStressFunction, i.e. a function that
takes a column name as a parameter and returns the text contained in
this column having replaced some letters of the text (the letters with
stress). Unfortunately, I could not do that because user-defined
functions cannot return a value of ntext.
>
So I have the following idea:
"select * from MyTable where
CheckIfTheyAreEqualIngoringTheStesses(MyNtextColum n, '" & MyString &
"')"
>
How can I implement the CheckIfTheyAreEqualIngoringTheStesses
function? (I don't know how to combine these functions to do what I
want: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT)

I will have to admit that I don't really follow what this
CheckIfTheyAreEqualIngoringTheStesses is supposed to achieve. But
there are a lot of problems working with ntext. In SQL 2005 there
is a new data type nvarchar(MAX) which has the same limit as ntext,
but without the limitations.

However, if I understand you right, you want to make an accent-insensitive
comparision, so that "rsum" = "resume". This you can do easily without
any replace business, just use an accent-insentive collation:

SELECT * FROM MyTable
WHERE MyNtextColumn
COLLATE Finnish_Swedish_CI_AI = ?

(As for the question mark, that's an indiciation that you should use
parameterised statements and not interpolate parameters into your SQL
commands.)

Note that Finnish_Swedish_CI_AI is just an example, and you should pick
the CI_AI collation that matches the language(s) you work with.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||It is just what I needed. Thanks a lot.

On Nov 29, 12:42 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(ver...@.hotmail.com) writes:

Quote:

Originally Posted by

I am running this query to an sql server 2000 database from my asp
code:
"select * from MyTable where
MySqlServerRemoveStressFunction(MyNtextColumn) = '" &
MyAdoRemoveStressFunction(MyString) & "'"

>

Quote:

Originally Posted by

The problem is that the replace function doesn't work with the ntext
datatype (so as to replace the stresses with an empty string). I had
to implement the MySqlServerRemoveStressFunction, i.e. a function that
takes a column name as a parameter and returns the text contained in
this column having replaced some letters of the text (the letters with
stress). Unfortunately, I could not do that because user-defined
functions cannot return a value of ntext.

>

Quote:

Originally Posted by

So I have the following idea:
"select * from MyTable where
CheckIfTheyAreEqualIngoringTheStesses(MyNtextColum n, '" & MyString &
"')"

>

Quote:

Originally Posted by

How can I implement the CheckIfTheyAreEqualIngoringTheStesses
function? (I don't know how to combine these functions to do what I
want: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT)

>
I will have to admit that I don't really follow what this
CheckIfTheyAreEqualIngoringTheStesses is supposed to achieve. But
there are a lot of problems working with ntext. In SQL 2005 there
is a new data type nvarchar(MAX) which has the same limit as ntext,
but without the limitations.
>
However, if I understand you right, you want to make an accent-insensitive
comparision, so that "rsum" = "resume". This you can do easily without
any replace business, just use an accent-insentive collation:
>
SELECT * FROM MyTable
WHERE MyNtextColumn
COLLATE Finnish_Swedish_CI_AI = ?
>
(As for the question mark, that's an indiciation that you should use
parameterised statements and not interpolate parameters into your SQL
commands.)
>
Note that Finnish_Swedish_CI_AI is just an example, and you should pick
the CI_AI collation that matches the language(s) you work with.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -