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

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-30
dita:mapPath
tpt1555966086716.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantage™

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