16.20 - Example: Differences Between SUBSTR and SUBSTRING - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Functions, Expressions, and Predicates

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

Consider the following table:

   CREATE TABLE ctable1
     (c1 VARCHAR(11) CHARACTER SET KANJI1);

The following table shows the difference between SUBSTR and SUBSTRING in Teradata mode for KANJI1 strings from KanjiEBCDIC client character set.

IF c1 contains … THEN this query … Returns …
MN<ABC >P SELECT SUBSTR(c1,2) FROM ctable1; N<ABC>P
SELECT SUBSTR(c1,3,8) FROM ctable1; <ABC>
SELECT SUBSTR(c1,4) FROM ctable1; ABC >P
The client application might not be able to properly interpret the resulting multibyte characters because the shift out (<) is missing.
SELECT SUBSTRING(c1 FROM 2)

FROM ctable1;

N<ABC>P
SELECT SUBSTRING(c1 FROM 3 FOR 8)

FROM ctable1;

<ABC>P
SELECT SUBSTRING(c1 FROM 4)

FROM ctable1;

<BC>P