Preface
The Transformation Language
Constants
Operators
Variables
- Built-in Variables
  - SYSDATE
    - Example
- Local Variables
Dates
Functions
- Function Categories
  - Aggregate Functions
  - Aggregate Functions and Nulls
    - Filter Conditions
  - Character Functions
  - Complex Functions
  - Conversion Functions
  - Data Cleansing Functions
  - Date Functions
  - Encoding Functions
  - Financial Functions
  - Numeric Functions
  - Scientific Functions
  - Special Functions
  - String Functions
  - Test Functions
  - Window Functions
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- ANY
- ARRAY
- ASCII
- AVG
- CAST
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COLLECT_LIST
- COLLECT_MAP
- COMPRESS
- CONCAT
- CONCAT_ARRAY
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CREATE_TIMESTAMP_TZ
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- EXTRACT_STRUCT
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GET_TIMEZONE
- GET_TIMESTAMP
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- ISNULL
- IS_DATE
- IS_NUMBER
- IS_SPACES
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAP
- MAP_FROM_ARRAYS
- MAP_KEYS
- MAP_VALUES
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PARSE_JSON
- PARSE_XML
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- RESPEC
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SIGN
- SIN
- SINH
- SIZE
- SOUNDEX
- SQL_LIKE
- SQRT
- STDDEV
- STRUCT
- STRUCT_AS
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TIME_RANGE
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_DECIMAL38
- TO_FLOAT
- TO_INTEGER
- TO_TIMESTAMP_TZ
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- UUID4
- UUID_UNPARSE
- VARIANCE

Transformation Language Reference

10.5.1
- 10.5.6
- 10.5.4
- 10.5
- 10.4.0

Back Next

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

Rename Saved Search

Table of Contents

Transformation Language Reference

Transformation Language Reference

SOUNDEX

SOUNDEX

Syntax

Return Value

Example