functions DIFFERENCE() and SOUNDEX()

Hi!
Is there any other function that can compares two strings ? I'm using the
functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" and
"h", and I need something that compares everything!
thanks
--
Message posted via http://www.sqlmonster.comOn Fri, 09 Sep 2005 15:37:43 GMT, Amaury Coria via SQLMonster.com wrote:
>Hi!
>Is there any other function that can compares two strings ? I'm using the
>functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" and
>"h", and I need something that compares everything!
>thanks
Hi Amaury,
What exactly do you mean with "compares everything"? If you are looking
for completely equal strings, just use the '=' operator. The SOUNDEX and
DIFFERENCE functions are deliberately leaving out certain parts of the
string, since they are intended to find common misspelling of words or
names. And in case you and/or your users are not English, beware that
they are designed for English.
If you want to find ""almost equal" strings but are not satisfied with
the algorithm used in SOUNDEX and DIFFERENCE, you'll have to create your
own functions for it. I have no experience with this kind of string
handling, but I believe that several algorithms for this kind of task
are out there on the internet. Google is your friend!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||There's an interesting article about alternative (better) soundex-like
schemes at http://www.avotaynu.com/soundex.html.
Paul Shapiro
"Amaury Coria via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:541CE9D86DFA7@.SQLMonster.com...
> Is there any other function that can compares two strings ? I'm using the
> functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y"
> and
> "h", and I need something that compares everything!

functions DIFFERENCE() and SOUNDEX()

Hi!
Is there any other function that can compares two strings ? I'm using the
functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" an
d
"h", and I need something that compares everything!
thanks
Message posted via http://www.droptable.comOn Fri, 09 Sep 2005 15:37:43 GMT, Amaury Coria via droptable.com wrote:

>Hi!
>Is there any other function that can compares two strings ? I'm using the
>functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" a
nd
>"h", and I need something that compares everything!
>thanks
Hi Amaury,
What exactly do you mean with "compares everything"? If you are looking
for completely equal strings, just use the '=' operator. The SOUNDEX and
DIFFERENCE functions are deliberately leaving out certain parts of the
string, since they are intended to find common misspelling of words or
names. And in case you and/or your users are not English, beware that
they are designed for English.
If you want to find ""almost equal" strings but are not satisfied with
the algorithm used in SOUNDEX and DIFFERENCE, you'll have to create your
own functions for it. I have no experience with this kind of string
handling, but I believe that several algorithms for this kind of task
are out there on the internet. Google is your friend!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||There's an interesting article about alternative (better) soundex-like
schemes at http://www.avotaynu.com/soundex.html.
Paul Shapiro
"Amaury Coria via droptable.com" <forum@.droptable.com> wrote in message
news:541CE9D86DFA7@.droptable.com...
> Is there any other function that can compares two strings ? I'm using the
> functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y"
> and
> "h", and I need something that compares everything!

functions DIFFERENCE() and SOUNDEX()

Hi!
Is there any other function that can compares two strings ? I'm using the
functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" and
"h", and I need something that compares everything!
thanks
Message posted via http://www.droptable.com
On Fri, 09 Sep 2005 15:37:43 GMT, Amaury Coria via droptable.com wrote:

>Hi!
>Is there any other function that can compares two strings ? I'm using the
>functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" and
>"h", and I need something that compares everything!
>thanks
Hi Amaury,
What exactly do you mean with "compares everything"? If you are looking
for completely equal strings, just use the '=' operator. The SOUNDEX and
DIFFERENCE functions are deliberately leaving out certain parts of the
string, since they are intended to find common misspelling of words or
names. And in case you and/or your users are not English, beware that
they are designed for English.
If you want to find ""almost equal" strings but are not satisfied with
the algorithm used in SOUNDEX and DIFFERENCE, you'll have to create your
own functions for it. I have no experience with this kind of string
handling, but I believe that several algorithms for this kind of task
are out there on the internet. Google is your friend!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||There's an interesting article about alternative (better) soundex-like
schemes at http://www.avotaynu.com/soundex.html.
Paul Shapiro
"Amaury Coria via droptable.com" <forum@.droptable.com> wrote in message
news:541CE9D86DFA7@.droptable.com...
> Is there any other function that can compares two strings ? I'm using the
> functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y"
> and
> "h", and I need something that compares everything!

Function that returns highest of two columns?

Is there a function that compares two columns in a row and will return
the highest of the two values? Something like:

Acct Total_Dollars Collected Total_Dollars_Due
11233 900.00 1000.00

Declare @.Value as money
set @.Value=GetHighest(Total_Dollars_Collected,TotalDol lars_Due)
Print @.Value

This function will return 1000.00 or the Total_dollars_Due??

Is there such a creature?On 21 Sep 2004 08:41:16 -0700, Philip Mette wrote:

>Is there a function that compares two columns in a row and will return
>the highest of the two values? Something like:
>Acct Total_Dollars Collected Total_Dollars_Due
>11233 900.00 1000.00
>Declare @.Value as money
>set @.Value=GetHighest(Total_Dollars_Collected,TotalDol lars_Due)
>Print @.Value
>
>This function will return 1000.00 or the Total_dollars_Due??
>Is there such a creature?

Hi Philip,

No. But you can create a user-defined function, if you wish. Or simply use
a CASE expression:

SET @.Value = CASE WHEN a > b THEN a ELSE b END

The user-defined function might be more friendly to the eyes. Using the
CASE expression wherever you need it might be a little bit less readable,
but it will probably perform better.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Is there a function that compares two columns in a row and will
return the highest of the two values? <<

in Oracle, there is a general GREATEST(<list>) function, but in
Standard SQL and T-SQL you nedd to use a CASE expression.

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 -