Table of Contents

Search

  1. Preface
  2. Function reference
  3. Constants
  4. Operators
  5. Dates
  6. Functions
  7. System variables
  8. Datatype reference

Function Reference

Function Reference

METAPHONE

METAPHONE

Encodes string values. You can specify the length of the string that you want to encode.
METAPHONE encodes characters of the English language alphabet (A-Z). It encodes both uppercase and lowercase letters in uppercase.
METAPHONE encodes characters according to the following list of rules:
  • Skips vowels (A, E, I, O, and U) unless one of them is the first character of the input string. METAPHONE(‘CAR’) returns ‘KR’ and METAPHONE(‘AAR’) returns ‘AR’.
  • Uses special encoding guidelines.
The following table lists the METAPHONE encoding guidelines:
Input
Returns
Condition
Example
B
n/a
When it follows M.
METAPHONE (‘Lamb’) returns LM.
B
In all other cases.
METAPHONE (‘Box’) returns BKS.
C
X
When followed by IA or H.
METAPHONE (‘Facial’) returns FXL.
S
When followed by I, E, or Y.
METAPHONE (‘Fence’) returns FNS.
n/a
When it follows S, and is followed by I, E, or Y.
METAPHONE (‘Scene’) returns SN.
K
In all other cases.
METAPHONE (‘Cool’) returns KL.
D
J
When followed by GE, GY, or GI.
METAPHONE (‘Dodge’) returns TJ.
T
In all other cases.
METAPHONE (‘David’) returns TFT.
F
F
In all cases.
METAPHONE (‘FOX’) returns FKS.
G
F
When followed by H and the first character in the input string is not B, D, or H.
METAPHONE (‘Tough’) returns TF.
n/a
When followed by H and the first character in the input string is B, D, or H.
METAPHONE (‘Hugh’) returns HF.
J
When followed by I, E or Y and does not repeat.
METAPHONE (‘Magic’) returns MJK.
K
In all other cases.
METAPHONE(‘GUN’) returns KN.
H
H
When it does not follow C, G, P, S, or T and is followed by A, E, I, or U.
METAPHONE (‘DHAT’) returns THT.
n/a
In all other cases.
METAPHONE (‘Chain’) returns XN.
J
J
In all cases.
METAPHONE (‘Jen’) returns JN.
K
n/a
When it follows C.
METAPHONE (‘Ckim’) returns KM.
K
In all other cases.
METAPHONE (’Kim’) returns KM.
L
L
In all cases.
METAPHONE (‘Laura’) returns LR.
M
M
In all cases.
METAPHONE (‘Maggi’) returns MK.
N
N
In all cases.
METAPHONE (‘Nancy’) returns NNS.
P
F
When followed by H.
METAPHONE (‘Phone’) returns FN.
P
In all other cases.
METAPHONE (‘Pip’) returns PP.
Q
K
In all cases.
METAPHONE (‘Queen’) returns KN.
R
R
In all cases.
METAPHONE (‘Ray’) returns R.
S
X
When followed by H, IO, IA, or CHW.
METAPHONE (‘Cash’) returns KX.
S
In all other cases.
METAPHONE (‘Sing’) returns SNK.
T
X
When followed by IA or IO.
METAPHONE (‘Patio’) returns PX.
0 *
When followed by H.
METAPHONE (‘Thor’) returns 0R.
n/a
When followed by CH.
METAPHONE (‘Glitch’) returns KLTX.
T
In all other cases.
METAPHINE (‘Tim’) returns TM.
V
F
In all cases.
METAPHONE (‘Vin’) returns FN.
W
W
When followed by A, E, I, O, or U.
METAPHONE (‘Wang’) returns WNK.
n/a
In all other cases.
METAPHONE (‘When’) returns HN.
X
KS
In all cases.
METAPHONE (‘Six’) returns SKS.
Y
Y
When followed by A, E, I, O, or U.
METAPHONE (‘Yang’) returns YNK.
n/a
In all other cases.
METAPHONE (‘Bobby’) returns BB.
Z
S
In all cases.
METAPHONE (‘Zack’) returns SK.
* The integer 0.
  • Skips the initial character and encodes the remaining string if the first two characters of the input string have one of the following values:
    • KN.
      For example, METAPHONE(‘KNOT’) returns ‘NT’.
    • GN.
      For example, METAPHONE(‘GNOB’) returns ‘NB’.
    • PN.
      For example, METAPHONE(‘PNRX’) returns ‘NRKS’.
    • AE.
      For example, METAPHONE(‘AERL’) returns ‘ERL’.
  • If a character other than “C” occurs more than once in the input string, encodes the first occurrence only. For example, METAPHONE(‘BBOX’) returns ‘BKS’ and METAPHONE(‘CCOX’) returns ‘KKKS’.

Syntax

METAPHONE(
string
[,
length
] )
Argument
Required/
Optional
Description
string
Required
Must be a character string. Passes the value you want to encode. The first character must be a character in the English language alphabet (A-Z). You can enter any valid expression.
Skips any non-alphabetic character in
string
.
length
Optional
Must be an integer greater than 0. Specifies the number of characters in
string
that you want to encode. You can enter any valid expression.
When
length
is 0 or a value greater than the length of
string
, encodes the entire input string.
Default is 0.

Return Value

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

Examples

The following expression encodes the first two characters in EMPLOYEE_NAME column to a string:
METAPHONE( EMPLOYEE_NAME, 2 )
Employee_Name
Return Value
John
JH
*@#$
NULL
P$%%oc&&KMNL
PK
The following expression encodes the first four characters in EMPLOYEE_NAME column to a string:
METAPHONE( EMPLOYEE_NAME, 4 )
Employee_Name
Return Value
John
JHN
1ABC
ABK
*@#$
NULL
P$%%oc&&KMNL
PKKM

0 COMMENTS

We’d like to hear from you!