Caché SQL Reference
$TRANSLATE
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

A string function that performs character-for-character replacement.
Synopsis
$TRANSLATE(string,identifier[,associator])
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 Optional — 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.
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.
$TRANSLATE is case-sensitive.
$TRANSLATE cannot be used to replace NULL with a character.
$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.
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'
 
See Also