Oracle/SQL SOUNDEX Function

SQL SOUNDEX FunctionOracle SOUNDEX Function is used to get the phonetic (the way it sounds) representation of any string.SQL SOUNDEX Function syntax and examples are mentioned below.


Oracle SOUNDEX Function Syntax

SELECT SOUNDEX(string_1)1
FROM table_name;

In the above SQL SOUNDEX Function Syntax:-

  • string_1 is the string whose phonetic string will be returned.
  • SOUNDEX Function in Oracle SQL / PLSQL will always return the 1st letter of the string as the first letter of the returned string.
  • Only first 5 consonants are used for determining the NUMERIC portion of the SOUNDEX return value, except when the 1st letter of the string_1 is a vowel.
  • SOUNDEX function is case insensitive and hence returns the same SOUNDEX pattern for UPPER and LOWER case strings.

Oracle SOUNDEX Function – Use with SQL SELECT Statememt

Oracle SOUNDEX Function is used with the SQL SELECT Statement.

For example, the SQL SOUNDEX Function query below return the phonetic representation of the string passed.

SELECT SOUNDEX('Tech Honey')
FROM dual;

The above SQL SOUNDEX Function will return ‘T250’.


Oracle SOUNDEX Function – Using Single Word Example

Oracle SOUNDEX Function can return phonetic representation of single word also.

For example, the SQL SOUNDEX Function returns the phonetic representation of the word passed.

SELECT SOUNDEX('Tech')
FROM dual;

The above SQL SOUNDEX Function returns ‘T200’.

Oracle SOUNDEX Function – Use with Table Column Example

Oracle SOUNDEX Function can be used on column of a table.

For example, the SQL SOUNDEX Function query below return the phonetic version of department names from employee table.

SELECT SOUNDEX(department)
FROM employee;

Tagged , , , , , , , , , , , . Bookmark the permalink.