$TRANSLATE (SQL)
Synopsis
$TRANSLATE(string,identifier[,associator])
Description
The $TRANSLATE function performs character-for-character replacement within a return value string. It processes the string argument one character at a time. It compares each character in string with each character in the identifier argument. If $TRANSLATE finds a match, it makes note of the position of that character.
-
The two-argument form of $TRANSLATE removes all instances of the characters in the identifier argument from the output string.
-
The three-argument form of $TRANSLATE replaces all instances of each identifier character found in the string with the positionally corresponding associator character. Replacement is performed on a character, not a string, basis. If the identifier argument contains more characters than the associator argument, the excess characters in the identifier argument are deleted from the output string. If the identifier argument contains fewer characters than the associator argument, the excess character(s) in the associator argument are ignored.
$TRANSLATE is case-sensitive.
$TRANSLATE cannot be used to replace NULL with a character.
SQLCODE -380 is issued if you specify too few arguments. SQLCODE -381 is issued if you specify too many arguments.
$TRANSLATE and REPLACE
$TRANSLATE performs character-for-character matching and replacement. REPLACE performs string-for-string matching and replacement. REPLACE can replace a single specified substring of one or more characters with another substring, or remove multiple instances of a specified substring. $TRANSLATE can replace multiple specified characters with corresponding specified replacement characters.
By default, both functions are case-sensitive, start at the beginning of string, and replace all matching instances. REPLACE has arguments that can be used to change these defaults.
Arguments
string
The target string. It can be a field name, a literal, a host variable, or an SQL expression.
identifier
The character(s) to search for in string. It can be a string or numeric literal, a host variable, or an SQL expression.
associator
An optional argument. The replacement character(s) corresponding to each character in the identifier. It can be a string or numeric literal, a host variable, or an SQL expression.
Examples
In the following example, a two-argument $TRANSLATE modifies Name values by removing punctuation (commas, spaces, periods, apostrophes, hyphens), returning names that consist of only alphabetic characters. Note that the identifier doubles the apostrophe to escape it as a literal character, rather than a string delimiter:
SELECT TOP 20 Name,$TRANSLATE(Name,', .''-') AS AlphaName
FROM Sample.Person
WHERE Name %STARTSWITH 'O'
In the following example, a three-argument $TRANSLATE modifies Name values by replacing commas and spaces with caret (^) characters, returning names delimited in three pieces (surname, first name, middle initial). Note that the associator must specify “^” as many times as the number of characters in identifier:
SELECT TOP 20 Name,$TRANSLATE(Name,', ','^^') AS PiecesNamePunc
FROM Sample.Person
WHERE Name %STARTSWITH 'O'
In the following example, a three-argument $TRANSLATE modifies Name values by both replacing commas and spaces with caret (^) characters (specified in the identifier and associator) and removing periods, apostrophes, and hyphens (specified in the identifier, omitted from the associator):
SELECT TOP 20 Name,$TRANSLATE(Name,', .''-','^^') AS PiecesNameNoPunc
FROM Sample.Person
WHERE Name %STARTSWITH 'O'