Teradata Learning- Substring and Concatenation(02/26/2013)

SUBSTRING: ANSI intermediate level compliant. Teradata version of this function is called SUBSTR.

SUBSTRING (string FROM startvalue FOR length)

Example 1:

SELECT SUBSTRING('GLOBALIZATION' FROM 5 FOR 3);

Result:ALI

Example 2:

SELECT SUBSTRING('GLOBALIZATION' FROM 0 FOR 4);

Result: GLO

Normally we would expect 4 character result , but the result is GLO because the 1st character(G) gets subscript 1.

		G	L	O	B	A	L	I	Z	A	T	I	O	N
-1	0	1	2	3	4	5	6	7	8	9	10	11	12	13

SELECT SUBSTRING('GLOBALIZATION' FROM -1 FOR 4);

Result: GL

		G	L	O	B	A	L	I	Z	A	T	I	O	N
-1	0	1	2	3	4	5	6	7	8	9	10	11	12	13

Example 3:

SELECT SUBSTRING('GLOBALIZATION' FROM 24 FOR 4);

Since 24 is greater than the length of the input string, we get a 0 length string output.

SELECT LENGTH(SUBSTRING('GLOBALIZATION' FROM 24 FOR 4)); --> gives 0

Example 4:

SELECT SUBSTRING('GLOBALIZATION' FROM 5 FOR 0);

Result: zero length string

Example 5:

SELECT SUBSTRING('GLOBALIZATION' FROM 1 FOR 0);

Result: Zero length string

Example 6:

SELECT SUBSTRING('GLOBALIZATION' FROM 4 FOR -3);

Result: SELECT Failed. 2662:  SUBSTR: string subscript out of bounds. 

The FOR part of the string is optional and can be left out.

When for is omitted it assumes end point as the end of string.

Example 7:

SELECT SUBSTRING('GLOBALIZATION' FROM 4);

Result :BALIZATION

Example 8:

SELECT SUBSTRING('GLOBALIZATION' FROM 0);

Result: GLOBALIZATION

Example 9:

SELECT SUBSTRING('GLOBALIZATION' FROM -1);

Result: GLOBALIZATION

		G	L	O	B	A	L	I	Z	A	T	I	O	N
-1	0	1	2	3	4	5	6	7	8	9	10	11	12	13

SUBSTR: Teradata extension.

The usage of SUBSTR is very much similar to SUBSTRING.

Only difference is in the syntax.

With SUBSTR we don't use the words FROM and FOR, Instead the numbers are written comma separated.

Example: SUBSTR('GLOBALIZATION',startvalue,length)

String Concatenation:

Concatenation operator is ||.

Only byte and Character data types can be concatenated.

SELECT 'GOD ' || ' IS GREAT';

Result : GOD  IS GREAT

Note :If either of the strings getting concatenated is NULL then the result of concatenation is also NULL.

SELECT 'GOD ' ||NULL;

Result : ? (NULL)

To avoid such cases we can use COALESCE to substitute with any other string in case the value is null for any column.

SELECT 'GOD ' ||COALESCE(NULL,'*NULLSTRING*');

Result: GOD *NULLSTRING*

Teradata extension for || is !!(double exclamation mark). Use of !! Works fine , however its suggested to use || as its ANSI compliant.

SELECT 'GOD ' !! ' IS GREAT';

Result: GOD  IS GREAT