Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Dates
  6. Functions
  7. Creating Custom Functions
  8. Custom Function API Reference
  9. POWERCENTERHELP

Transformation Language Reference

Transformation Language Reference

SOUNDEX

SOUNDEX

Encodes a string value into a four-character string.
SOUNDEX works for characters in the English alphabet (A-Z). It uses the first character of the input string as the first character in the return value and encodes the remaining three unique consonants as numbers.
SOUNDEX encodes characters according to the following list of rules:
  • Uses the first character in
    string
    as the first character in the return value and encodes it in uppercase. For example, both SOUNDEX(‘John’) and SOUNDEX(‘john’) return ‘J500’.
  • Encodes the first three unique consonants following the first character in
    string
    and ignores the rest. For example, both SOUNDEX(‘JohnRB’) and SOUNDEX(‘JohnRBCD’) return ‘J561’.
  • Assigns a single code to consonants that sound alike.
    The following table lists SOUNDEX encoding guidelines for consonants:
    SOUNDEX Encoding Guidelines for Consonants
    Code
    Consonant
    1
    B, P, F, V
    2
    C, S, G, J, K, Q, X, Z
    3
    D, T
    4
    L
    5
    M, N
    6
    R
  • Skips the characters A, E, I, O, U, H, and W unless one of them is the first character in
    string
    . For example, SOUNDEX(‘A123’) returns ‘A000’ and SOUNDEX(‘MAeiouhwC’) returns ‘M200’.
  • If
    string
    produces fewer than four characters, SOUNDEX pads the resulting string with zeroes. For example, SOUNDEX(‘J’) returns ‘J000’.
  • If
    string
    contains a set of consecutive consonants that use the same code listed in SOUNDEX, SOUNDEX encodes the first occurrence and skips the remaining occurrences in the set. For example, SOUNDEX(‘AbbpdMN’) returns ‘A135’.
  • Skips numbers in
    string
    . For example, both SOUNDEX(‘Joh12n’) and SOUNDEX(‘1John’) return ‘J500’.
  • Returns NULL if
    string
    is NULL or if all the characters in
    string
    are not letters of the English alphabet.

Syntax

SOUNDEX(
string
)
The following table describes the argument for this command:
Argument
Required/
Optional
Description
string
Required
Character string. Passes the string value you want to encode. You can enter any valid transformation expression.

Return Value

String.
NULL if one of the following conditions is true:
  • If value passed to the function is NULL.
  • No character in
    string
    is a letter of the English alphabet.
  • string
    is empty.

Example

The following expression encodes the values in the EMPLOYEE_NAME port:
SOUNDEX( EMPLOYEE_NAME )
EMPLOYEE_NAME
RETURN VALUE
John
J500
William
W450
jane
J500
joh12n
J500
1abc
A120
NULL
NULL