Function that replaces ntext and compares ntext with nvarchar

#1

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)

#2

(verb13 (AT) hotmail (DOT) com) writes:

Quote:

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 "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, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#3

It is just what I needed. Thanks a lot.

On Nov 29, 12:42 am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

(ver... (AT) hotmail (DOT) 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(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 "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... (AT) sommarskog (DOT) 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 -

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Function that replaces ntext and compares ntext with nvarchar

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Function that replaces ntext and compares ntext with nvarchar - 11-28-2007 , 01:28 PM

Re: Function that replaces ntext and compares ntext with nvarchar - 11-28-2007 , 04:42 PM

Re: Function that replaces ntext and compares ntext with nvarchar - 11-29-2007 , 05:28 PM