Teradata SUBSTRING or SUBSTR function extracts a substring from another string based on the position.
SUBSTRING(StringExpression FROM n1 FOR n2)
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.
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,
SELECT SUBSTR(c1,2) FROM ctable1;
SELECT SUBSTR(c1,4) FROM ctable1;
SELECT SUBSTRING(c1 FROM 2) FROM ctable1;
SELECT SUBSTRING(c1 FROM 4) FROM ctable1;