| String Comparison [message #671640] |
Tue, 11 September 2018 08:55  |
Duane
Messages: 585
Registered: December 2002
|
Senior Member |
|
|
What is the most efficient way to perform a string comparison on two variables? A simple string compare, hash or something else?
I'm trying to compare if two addresses in the database are the same. I need to compare the address of a household to see if the husband and wife have the same address. I'm thinking some type of hash compare but maybe I'm way off base on that.
Has anyone done something like what I'm wanting to do and done it with XYZ statement?
string1 varchar2(100) default '101 Oracle Dr.';
string2 varchar2(100) default '101 Oracle Dr.';
if string1 = string 2
then
'Equal'
else
'Not Equal'
end if;
|
|
|
|
|
|
| Re: String Comparison [message #671643 is a reply to message #671641] |
Tue, 11 September 2018 09:32  |
Duane
Messages: 585
Registered: December 2002
|
Senior Member |
|
|
BlackSwan wrote on Tue, 11 September 2018 14:15
Do the number of space characters matter?
Does letter CaSe Matter"
is "E Main" the same as "East Main"?
Is ZIP Code required or optional?
How close is close enough?
Those are good questions.
I'm going to use the data as entered by the data entry people with the exception of making the characters lower case. So, if the husband has 101 E Main and the wife has 101 East Main then the compare would not equal.
Zipcode is available if I need to use it.
|
|
|
|
|
|
| Re: String Comparison [message #671645 is a reply to message #671644] |
Tue, 11 September 2018 09:48 |
Duane
Messages: 585
Registered: December 2002
|
Senior Member |
|
|
BlackSwan wrote on Tue, 11 September 2018 14:37http://lmgtfy.com/?q=address+cleansing+open+source
There are commercial software products that provide same capability.
It is a non-trivial problem to get close to correct results.
I don't believe that answers my question of using some Oracle function/statement to do a string compare. I'm just wanting to know what other people are using that may be doing the same thing.
The data is what the data is.
|
|
|
|
| Re: String Comparison [message #671648 is a reply to message #671645] |
Tue, 11 September 2018 10:31 |
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Why do you need anything other than the obvious:
lower(column/variable) = lower(column/variable)
Basic equality checks is some of the most basic things oracle does.
|
|
|
|
| Re: String Comparison [message #671649 is a reply to message #671648] |
Tue, 11 September 2018 10:54 |
Duane
Messages: 585
Registered: December 2002
|
Senior Member |
|
|
cookiemonster wrote on Tue, 11 September 2018 15:31Why do you need anything other than the obvious:
lower(column/variable) = lower(column/variable)
Basic equality checks is some of the most basic things oracle does.
That's why I was asking. I didn't know if there was a faster more efficient way of doing it. As I said, maybe hashing the first 20-30 characters and then doing a compare on the hash. I don't know. Maybe Oracle can do that quicker than doing lower(column/variable) = lower(column/variable).
If that's all I need to do then, yes, I'll do it. If it's faster to do it some other way then I want to hear about it. I don't know until I ask.
|
|
|
|
| Re: String Comparison [message #671650 is a reply to message #671649] |
Tue, 11 September 2018 10:59 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Unless you use linguistic comparison, Oracle compares strings byte-by-byte left-to-right, so it will stop comparing as soon as it finds first byte that differs.
SY.
|
|
|
|
| Re: String Comparison [message #671661 is a reply to message #671649] |
Wed, 12 September 2018 03:42 |
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Oracle does string comparisons very quickly.
Even if comparing hashes was noticeably faster (and I suspect you'd need something longer than an address for that to be true) the speed improvement would be eradicated by the time spent hashing the data for the comparison.
I suggest you code up a standard comparison and see how long it takes. If it's too slow then start looking at ways to speed it up.
|
|
|
|
| Re: String Comparison [message #671662 is a reply to message #671661] |
Wed, 12 September 2018 04:06 |
 |
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:Oracle does string comparisons very quickly.
Strings comparison is a single CPU instruction in most of current processors.
Here's Oracle doc for Solaris x86.
Nothing can be faster (unless, as Solomon mentioned, you want to use linguistic comparison).
[Edit: typo]
[Updated on: Wed, 22 January 2020 11:41] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
