Group Discounts available for 3+ students and Corporate Clients

Functions in TeraData


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:

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at, we will update the article in 24 hours.”

0 Responses on Functions in TeraData"

Leave a Message

Your email address will not be published. Required fields are marked *


Please Enter Your Details and Query.
Three + 6