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’)


Description Getting the non- empty column value or control information.


Null LIF It returns null faith Arguments are equal


Syntax 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


TRIM  It removes both left and right side spaces


Syntax -TRIM (Column or expression)


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

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

TRIM (Both From[Expression]Leading an training blanks/brings zeros

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

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


Aspired to become a Teradata Developer? Explore the post to discover the know-hows on

Teradata Training Program.

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 sub string 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 party;


Add Months

Adding specified number of month to the date and column



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-Strubg)



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

Ex- Select party income as pi from 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


When party income<=30000 Then ’poor’

When party income>30000 And party income<50000

Then ’Avg’

Else ’Good’

End As Rating from party; 


Data types Attribute functions

Title - Display title of the column


Format - Display Format of the column


Named - Display Column name


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

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

Learn more about Teradata Interview Questions in this blog post.

Sample - Displays sample number of rows

1.Syntax - Select* From party sample N;

Ex -  Select * from party smple10;

  1. 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)AS R from party Quality R<5;


RANDOM - Displays the RANDOM value between range specified

Syntax - Random(Low value, High value)

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


Merge - It updates, if Record exist otherwise it insert

Ex - Merge in to party using values(1,’meera’,30)As D(pid,pnm)

On D. Pid- party id

When matched then

Up date 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 ouptpu is 003% then it displays3%

Select party in come(Format’XC3’)From party;

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

For Indepth knowledge on Teradata click on: