Home » SQL & PL/SQL » SQL & PL/SQL » String Comparison (Oracle 12.2 Windows)
String Comparison [message #671640] Tue, 11 September 2018 08:55 Go to next message
Duane
Messages: 557
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 #671641 is a reply to message #671640] Tue, 11 September 2018 09:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Duane wrote on Tue, 11 September 2018 06:55
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;
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?


Re: String Comparison [message #671643 is a reply to message #671641] Tue, 11 September 2018 09:32 Go to previous messageGo to next message
Duane
Messages: 557
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 #671644 is a reply to message #671643] Tue, 11 September 2018 09:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://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.
Re: String Comparison [message #671645 is a reply to message #671644] Tue, 11 September 2018 09:48 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
BlackSwan wrote on Tue, 11 September 2018 14:37
http://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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
cookiemonster wrote on Tue, 11 September 2018 15:31
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.
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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

Previous Topic: Help in Understanding a complex (complex at least for me) sql to solve sudoku
Next Topic: Processed time for a Job SQL
Goto Forum:
  


Current Time: Thu Mar 28 07:30:55 CDT 2024