Posted by verb13 on 11/29/07 23:28

It is just what I needed. Thanks a lot.

On Nov 29, 12:42 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (ver...@hotmail.com) writes:
> > 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(MyNtextColumn, '" & 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 "résumé" = "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 -

[Back to original message]