SUBSTRING in Teradata

Teradata SUBSTRING function extracts a part of the string based upon the starting position and the number of characters to extract.

SELECT SUBSTRING ('BCSF16M036' FROM 8 FOR 3) as roll_num

roll_num
----------
036

Definition and Usage

Teradata SUBSTRING function extracts a substring from a string. We can start extracting a substring from any of the positions in a string.

Syntax

SUBSTRING (string FROM s1 FOR n);

FROM and FOR are reserved keywords.

Argument Types

Argument	Description
string	It represents the string to extract substring.
s1	It represents the starting position to extract the substring.
n	It represents the number of characters extracted from the string.

If the n argument is omitted the SUBSTRING function extracts all of the characters till the end of the string.

Result Value

This function returns a substring.

Examples:

For example, we have a student table that contains name, cgpa, id fields.

student
--------------------------
name 	cgpa	id

	2.1	BCSF16M001
	2.6	BSEF16M060
	3.1	BITF16M001
	3.7	BCSF16A001
	2.2	BSEF16A060
	3.5	BITF16A001

	2.3	BCSF17M001
	3.6	BSEF17M060
	3.1	BITF17M001
	2.0	BCSF17A001
	1.9	BSEF17A060
	2.3	BITF17A001

	1.9	BCSF18A060
	3.4	BSEF18M001
	2.4	BITF18M060
	3.2	BCSF18A001
	3.1	BSEF18A060	
	3.0	BITF18A001

	2.6	BCSF19M060
	3.1	BSEF19M001
	2.1	BITF19M060	
	2.6	BCSF19A060
	2.5	BSEF19A001
	2.2	BITF19A060

The 'id' column contains a lot of information of a student such as degree(CS, SE, IT), falling year(F16, F17,...), time slots (Morning or Afternoon), and the three letter roll number.

We'll use the id field to select a specific group of students.

Example 1:

The SUBSTRING function selects the students of BSE degree in the following query.

SELECT name, cgpa FROM student WHERE SUBSTRING(id FROM 1 FOR 3)='BSE'

Result:

student
--------------------------
name 	cgpa	id

	2.6	BSEF16M060
	2.2	BSEF16A060
	3.6	BSEF17M060
	1.9	BSEF17A060
	3.4	BSEF18M001
	3.1	BSEF18A060
	3.1	BSEF19M001
	2.5	BSEF19A001

Example 2:

The following query selects the fall 18 students.

SELECT name, cgpa FROM student WHERE SUBSTRING(id FROM 4 FOR 3)='F18'

Result:

student
--------------------------
name 	cgpa	id

	1.9	BCSF18A060
	3.4	BSEF18M001
	2.4	BITF18M060
	3.2	BCSF18A001
	3.1	BSEF18A060	
	3.0	BITF18A001

Example 3:

The following query selects the students of Morning time slot.

SELECT name, cgpa FROM student WHERE SUBSTRING(id FROM 7 FOR 1)='M'

Result:

student
--------------------------
name 	cgpa	id

	2.1	BCSF16M001
	2.6	BSEF16M060
	3.1	BITF16M001

	2.3	BCSF17M001
	3.6	BSEF17M060
	3.1	BITF17M001

	1.9	BCSF18A060
	3.4	BSEF18M001
	2.4	BITF18M060

	2.6	BCSF19M060
	3.1	BSEF19M001
	2.1	BITF19M060

Example 4:

The Teradata SUBSTRING function selects those students that have 060 roll number in the following query.

SELECT name, cgpa FROM student WHERE SUBSTRING(id FROM 8 FOR 3)='060'

Result:

student
--------------------------
name 	cgpa	id

	2.6	BSEF16M060
	3.6	BSEF17M060
	1.9	BSEF17A060
	1.9	BCSF18A060
	2.4	BITF18M060
	3.1	BSEF18A060	
	2.6	BCSF19M060
	2.1	BITF19M060	
	2.6	BCSF19A060
	2.2	BITF19A060

Example 5:

The SUBSTRING function extracts degree, year, time slot, and roll_num from id column of student table.

SELECT name, cgpa, 
 SUBSTRING(id FROM 1 FOR 3) AS degree, 
 SUBSTRING(id FROM 4 FOR 3) AS year, 
 SUBSTRING(id FROM 7 FOR 1) AS time, 
 SUBSTRING(id FROM 8 FOR 3) AS roll_num 
FROM student WHERE SUBSTRING(id FROM 1)='BSEF17M060'

Result:

student
-----------------------------------------------
name 	cgpa	degree	year	time	roll_num	

	3.6	BSE	F17	M	060

Summary

SUBSTRING function is handy to use in Teradata. It accepts the starting index and the number of characters to extract a part of the string.