Teradata SUBSTRING or SUBSTR

Teradata SUBSTRING or SUBSTR function extracts a substring from another string based on the position.

ANSI syntax

SUBSTRING(StringExpression FROM n1 FOR n2)

Teradata Syntax

SUBSTR(StringExpression, n1, n2)

Here, StringExpression is the string from which the substring is to be extracted. n1 is the starting position to extract from StringExpression. N2 is the length of the substring to extract from StringExpression. If your StringExpression is a BYTE or CHAR type then you can omit “FOR n2”, trailing binary zeros or pad characters are trimmed.

SUBSTRING and SUBSTR operate on the Character, Byte, Numeric and UDTs that have implicit cast to any of the previous. If the StringExpression is numeric, it is implicitly converted to Character type for this operation.

Examples of Teradata SUBSTRING or SUBSTR:

Suppose sn is a CHARACTER(15) field of Serial IDs for Automobiles and positions 3 to 5 represent the country of origin as three letters.

For example:

   12JAP3764-35421
   37USA9873-26189
   11KOR1221-13145

To search for serial IDs of cars made in the USA:

   SELECT make, sn 
   FROM autos 
   WHERE SUBSTRING (sn FROM 3 FOR 3) = 'USA';

If we want the last five characters of the serial ID, which represent manufacturing sequence number, another substring can be accessed using Teradata SUBSTRING or SUBSTR.

SELECT make, SUBSTRING (sn FROM 11) AS sequence 
FROM autos
WHERE SUBSTRING (sn FROM 3 FOR 3) = 'USA';

If C1 = MN<ABC>P,

Query:

SELECT SUBSTR(c1,2) FROM ctable1; 

Result:

N<ABC>P

Query:

SELECT SUBSTR(c1,4) FROM ctable1;

Result:

N<ABC>P

Query:

SELECT SUBSTRING(c1 FROM 2) 
FROM ctable1;

Result:

N<ABC>P

Query:

SELECT SUBSTRING(c1 FROM 4) 
FROM ctable1;

Result:

<BC>P