Function group: string function
TRANSLATE() transliterates, i.e. converts, an alphanumeric string into a national string or vice versa, see the “ Core manual”.
TRANSLATE() transcodes, i.e. converts, a string in the character set UTFE to a national string in the character set UTF-16 or vice versa, see the “ Core manual”.
TRANSLATE ( expression
USING [[ catalog. ]INFORMATION_SCHEMA.] transname [DEFAULT character ] [ ,length ])
character::= expression
length::= unsigned_integer
expression
Alphanumeric expression or national expression.
Its evaluation returns either an alphanumeric string or a national string. See also section "Compatibility between data types".
expression may not be a multiple value with dimension > 1.
transname
Unqualified Name for a transliteration of EBCDIC to Unicode (character set UTF-16) and vice versa or for a transcoding of UTF-EBCDIC to UTF-16 and vice versa.
In SESAM/SQL all transliteration names are predefined. They are either the CCS names which are defined in the BS2000 subsystem XHCS for transliteration between EBCDIC and UTF-16 or CATALOG_DEFAULT for transliteration in the preselected database if CODE_TABLE is not set to _NONE_ for the latter (see CREATE/ALTER CATALOG statements in the “ SQL Reference Manual Part 2: Utilities”). The CCS name can be up to 8 characters long.
When expression is an alphanumeric expression and the transliteration name UTFE (!) is specified, expression is transcoded from UTF-EBCDIC (character set UTFE) to the character set UTF-16.
When expression is a national expression (i.e. the character set is UTF-16) and the transliteration name UTFE is specified, expression is transcoded from UTF-16 to the character set UTFE.
Transliteration and transcoding can be qualified by a database name and the schema name INFORMATION_SCHEMA, otherwise the INFORMATION_SCHEMA of the predefined database is assumed.
character
With character you can define a substitute character which is to be output in place of characters which cannot be processed with the specified transname. If you have not specified DEFAULT character and expression contains a character that cannot be processed with the specified transname, the containing SQL statement is aborted with SQLSTATE.If expression has the alphanumeric data type CHAR or VARCHAR, the substitute character must have the national data type NCHAR(1) or NVARCHAR( n) with n>=1.If expression has the national data type NCHAR or NVARCHAR, the substitute character must have the alphanumeric data type CHAR(1) or VARCHAR( n) with n>=1.
length
Maximum length of the transliterated or transcoded string in code units.
1 <= length <= 16000 when expression is an alphanumeric string
(transliteration name is an EBCDIC character set or UTFE).
1 <= length <= 32000 when expression is a national string
(transliteration name is an EBCDIC character set).
Length not specified:
The result has the maximum possible length (see above).
Result
If expression and/or character return NULL, the result is NULL.
Otherwise:
The result is the string with the specified or maximum length which results from the transliteration or transcoding of expression.
If the substitute character had to be used in the transliteration, the warning SQLSTATE '01SBB' is issued.
When the length of the transliterated or transcoded string is greater than the specified or maximum length, the function is aborted with SQLSTATE.
Data type:
If expression has the alphanumeric data type CHAR( n) or VARCHAR( n), the result has the national data type NVARCHAR( n).
If expression has the national data type NCHAR or NVARCHAR, the result of the transliteration has the alphanumeric data type VARCHAR(n) and, in the case of transcoding, the national data type NVARCHAR(n) .
Examples
The specified national string is to be transliterated by transliterating EDF03IRV to the standard BS2000 character set. Non-displayable characters are represented as question marks.
TRANSLATE (NX'0041004200430308' USING
WORLD_CUST.INFORMATION_SCHEMA.EDF03IRV DEFAULT '?')
The result ist the string 'ABC?'.
The specified alphanumeric string is to be interpreted as a string with the character set UTF-EBCDIC and to be transcoded to the Unicode character set UTF-16.
TRANSLATE ('ABC' USING UTFE)
004100420043
Interprets a file NAMETITEL.TXT in the character set UTFE (created, e.g., with UNLOAD) as a CSV file.
CREATE VIEW MYVIEW(x,y) AS SELECT TRANSLATE(name USING UTFE), TRANSLATE(titel USING UTFE) FROM TABLE(CSV(FILE 'NAMETITEL.TXT' DELIMITER ';',CHAR(25),VARCHAR(16))) AS T(name,titel)