• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2480
  • Last Modified:

What is the Oracle equivalent of the DIFFERENCE function in MS SQL Server?

What is the equivalent SQL statement in Oracle for the following SQL statement in MS SQL Server using the DIFFERENCE function (related to SOUNDEX coding)?

SELECT     *
FROM         table
WHERE     DIFFERENCE(Surname, 'Smith') = 4

Some websites I have looked at point to UTL_MATCH functions, but when I try these, my system does not recognise the keywords.
2 Solutions
Naveen KumarProduction Manager / Application Support ManagerCommented:
There is function with name soundex in oracle.

url : http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions148.htm#i78853

The following example returns the employees whose last names are a phonetic representation of "Smyth":

SELECT last_name, first_name
     FROM hr.employees
     WHERE SOUNDEX(last_name)
         = SOUNDEX('SMYTHE');

---------- ----------
Smith      Lindsey
Smith      William

But i am not sure is that one you are looking for.

Mark GeerlingsDatabase AdministratorCommented:
Oracle supports the SOUNDEX operator (as nav_kum_v mentioned).  I have used that in the past to find names that sound like other names, even if they are spelled differently.  Is that what you are looking for?

If not, can you tell us what the DIFFERENCE function does in SQL Server?  (Many of us on this site don't use SQL Server, so we don't know what the various SQL Server functions do.)

I have never heard of or used utl_match (that is apparently new in Oracle10.2).  Which version of Oracle do you have?
MTechoAuthor Commented:
Thank you for your responses.

The DIFFERENCE function in SQL Server compares the SOUNDEX values of two strings and assigns a difference value from 0 to 4 based on their similarity.
A value of 0 is very dissimilar (about the first letter same)
A value of 4 is the highest value, representing strings that are quite similar.

A difference value of 4 represents string quite similar, but still less similar than the
method, giving a wider selection of names returned by the query.

Is it possible to use substrings of the SOUNDEX values or are the UTL_MATCH functions the only alternative?
What are the prerequisites for the Oracle database server to be able to use UTL_MATCH functions? (four functions included in the "package" in http://www.psoug.org/reference/utl_match.html)

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now