Functions in TeraData

  • Share this blog:


a)To do some operations by loading or retrieving [Arithmetic, mathematics, etc operations]

b) To implement “ETL” Functionality etc  


  • It returns 1st, not null value
  • It can be also be used for returning user Defined values in case of null  


Coalesce(Column or expression,<user define values>)  

Ex: Select party name, coalesce (party name, ’unknown’)From party  

Ex: coalesce(permanent, temporary, email id, contracting ‘Details not available’)  


Getting the non- empty column value or control information.  

Null LIF

It returns null faith Arguments are equal  


Null IF(Argument1,Argument2)  


  1. Select party income, Null if(party income,50000)from party’
  2. Select 100/(null if(10-10-10,0))—it prevents from division by zero


It removes both left and right side spaces  

Syntax- TRIM (Column or expression)

Ex-  Select party Name, TRIM (Party name) from the party;

TRIM ([Expression] Leading and training Blanks/Binary zeros

TRIM (Both From[Expression]Leading a training blank/brings zeros

Trim (Train LING From[Expression])Trailing blanks/binary zeros

Trim(Leading from[Expression]) Leading blanks/binary zeros   

Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore Teradata Training

Character or chars

Displays the No.of character in the string  

Syntax- Chars (Column or expression)  

Ex: Select party name, chars(party name) from party  


Display the length of the argument[Can be integer, string, date]  

Syntax- Length(Argument)

Ex: Select Length(‘Vinay’)

Select Length(‘100’)  

Sub str

Display substring from the specified to position to the length in the main string  

Syntax- Sub str(Column or expression, startpos, Length)

Ex- Select party name, sub str(party name 3,2)From party;  


Display the first occurrence position of a character or string in columns expression

Syntax- Position(Character or string in column or expression)

Ex: Select party name, Position(‘a’ in party name)from the party;  

Add Months

Adding a specified number of month to the date and column  

Syntax- Add-months(Date column,<No of Months>)

Ex- Select Jdate, Add- months (jadate,5) from party  


It converts one data type to another data type [Integer to string date to string etc]

Syntax- CAST(Column Or Exp As new data type);


  1. Select Jdate, CAST(J Date As varchar(7))From pary—Date—String
  2. Select CAST(‘1234’ As char(3)--; Integer-String)


It used for taking alias name and we can use alias anywhere in the query

Ex- Select party income as pi from a party where pi<50000;   

CASE Expression

It Evaluates Against multiple conditions[Instead of multiple id else conditions we go for this]  

Syntax- CASE

When condition1 Then STMT1

When condition2 Then STMT2

When condition3 then STMT3




Select party id, party name, party income CASE When party income<=30000 Then ’poor’ When party income>30000 And party income<50000 Then ’Avg’ Else ’Good’ End As Rating from the party;   

Data types Attribute functions

Title - Display title of the column  

Format - Display Format of the column  

Named - Display Column name  

Type - Display The data type of the column if there no title column name is the title

Select title(party name), Format(Party name), named(party name), type(party name )from the party;

Teradata Interview Questions


  • Displays sample number of rows

Syntax: Select* From party sample N;

Ex:  Select * from party smple10;

  • Displays sample present age number of rows

Syntax: Select*from party sample N;

Ex: Select * from party sample 20;


Displays the party Rank of the column

Select party income, Rank(Party income)ASR from party Quality R<5;  


Displays the RANDOM value between range specified

Syntax - Random(Low value, High value)

Ex - Select party id, party name, Random(20,30) from the party;  


It updates, if the Record exists otherwise it inserts

Ex - Merge into a party using values(1,’Meera’,30)As D(pid,pnm)

On D. Pid- party id

When matched then

Update set party=D.PNM

When Not varchar then

Insert values(, D. PNM, D. ped)  

Format commend

It is used to change the format of integer, strings, and dates

Ex: Select party income, party income(Format’$99,999,99’)

From party;

Select(1000/party income)*100(Format ‘z9%’)As

“Increase percentage” From party;-- it the output is 003% then it displays3%

Select party income(Format’ XC3’)From party;

Select JDate, Jdate(Format ’yyyyBmmm/DD’)From party;

For In-depth knowledge on Teradata click on:

About Author
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.