Saturday, 14 February 2015

String Functions in Teradata

In Teradata there are many String functions. In simple terms, String functions are which deal with Characters.

There are 2 modes are available. One is ANSI mode and another one is Teradata mode. ANSI mode is CASE Sensitive. Teradata mode is not CASE sensitive.

Understanding and learning of STRING functions is very important in Data warehouse projects.

The following functions are available:

CHARACTERS
SUBSTR
SUBSTRING
TRIM
POSITION and INDEX

In Teradata mode:

SELECT CHARACTERS(PRODUCT_NAME) AS LENGTH_PRODUCT FROM SAMPLES.ACCOUNTS;

In ANSI mode:

SELECT CHARACTER_LENGTH(PRODUCT_NAME) AS LENGTH_PRODUCT FROM SAMPLES.ACCOUNTS;

The output we will have number of Characters in Product_name;

SELECT TRIM(PRODUCT_NAME) AS NEW_NAME FROM SAMPLES.ACCOUNTS;

TRIM-functions deletes spaces on both sides of Product_name.

Challenge is how to delete spaces in between name of the product. Here we need to use SUBSTRING concept.





No comments:

Post a Comment