STRING_CS Function Examples | VantageCloud Lake - STRING_CS Function Examples - Teradata Vantage

Example: Using STRING_CS to Determine the Client Character Set

Consider the following table definition:

CREATE TABLE SysNames
   (SysID INTEGER
   ,SysName VARCHAR(30) CHARACTER SET KANJI1);

Suppose the session character set is KANJIEBCDIC5026_0I. The following statement inserts the mixed single-byte/multibyte character string '<TEST >Q' into the SysName column of the SysNames table:

INSERT SysNames (101, '0E42E342C542E242E30FD8'XC);

Using STRING_CS to determine the client character set that was used to encode the string produces the results that follow:

SELECT STRING_CS(SysName) FROM SysNames WHERE SysID = 101;

String_CS(SysName)
------------------
                 1

Example: Using STRING_CS to Translate a KANJI1 String to UNICODE

Consider the SysNames table from the preceding example.

The following statement uses STRING_CS to determine which encoding to use to translate strings in the SysName column from the KANJI1 server character set to the UNICODE server character set:

   SELECT CASE STRING_CS(SysName)
      WHEN 0 THEN TRANSLATE(SysName USING KANJI1_SBC_TO_UNICODE)
      WHEN 1 THEN TRANSLATE(SysName USING KANJI1_KANJIEBCDIC_TO_UNICODE)
      WHEN 2 THEN TRANSLATE(SysName USING KANJI1_KANJIEUC_TO_UNICODE)
      WHEN 3 THEN TRANSLATE(SysName USING KANJI1_KANJISJIS_TO_UNICODE)
      ELSE TRANSLATE(SysName USING KANJI1_SBC_TO_UNICODE)
      END
   FROM SysNames;