Be it a programming language or the querying language, the substring function is one of the most commonly used ones. The need of extracting the particular portion in a string is much needed while working with the data especially when the contents are of the same length. This will help a programmer to extract a piece of meaningful information from a large string. Let us see the type of substring functions in Teradata.
Teradata has two functions to get this done.
- SUBSTRING – ANSI SQL-2011 compliant function
- SUBSTR – Teradata extended ANSI SQL-2011 compliant function
Supported data types
Both these functions support Character, Byte, and Numeric data types. There will be an implicit conversion into characters for the Numeric data types. Let us see each of these Teradata substring functions with examples.
Syntax
SUBSTRING(String expression, starting position, number of characters) SUBSTR(String expression, starting position, number of characters)
Example 1 – Substring using the SUBSTRING function
Let us see an example to understand this function better. For example, there is a column in a table that holds the license number of people in a specific format. This could be because the source system has stored the information in that manner. During data migration, if we want to extract and keep them in separate columns, the substring function would be very useful.
Consider that the license number is stored in a column of a table in the database as shown below
INDIA-TN-2020-ABC12345(COUNTRY-STATE-LICENSED)
In this, if you want to extract the state information, you can use the substring function
SELECT SUBSTRING('INDIA-TN-ABC12345', 7,2);
Output
Example 2 – Substring using the SUBSTR function
Suppose, you are storing the name of the log file in a column of a table for auditing purposes like below. In that, if you want to extract the DATE value alone, you can use the SUBSTR function to extract that.
SELECT SUBSTRING('logfile_20201002_database.log',9,8) as LogDate;
Output
Also read,