Can you really decide matching area of two string using code return by soundex purpose of plsql ?

Soundex is not terribly reliable for matching strings, especially after they are gone a particular length, you might like to consider the UTL_MATCH package options.

You could utilize the Jaro-Winkler method within the UTL_MATCH package. There's an identical question on Here: How can I use jaro-winkler to find the closest value in a table?

The UTL_MATCH paperwork are here: http://docs.oracle.com/cd/E14072_01/appdev.112/e10577/u_match.htm

There's good quality paperwork on matching strings here too: http://docs.oracle.com/cd/E11882_01/owb.112/e10935/match_merge.htm

Hope this can help...

The main difference function converts two strings for their Soundex codes after which reviews the amount of matching code positions. Since Soundex codes have four figures, the end result ranges from zero to four, with zero being no match and four becoming an exact match. (Thus, the function is misnamed — similarity will be a better title.)

SELECT * FROM s WHERE difference(s.nm, 'john') > 2;