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

Posted on 2007-09-30
Last Modified: 2008-01-09
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.
Question by:MTecho
    LVL 28

    Accepted Solution

    There is function with name soundex in oracle.

    url :

    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.

    LVL 4

    Expert Comment

    LVL 34

    Assisted Solution

    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?

    Author Comment

    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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now