17.10 - Example: Differences Between SUBSTR and SUBSTRING - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1145-171K
Language
English (United States)

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