Working with Built-in Functions

Practice Labs Module
Time
38 minutes
Difficulty
Intermediate

The "Working with Built-in Functions" module provides you with the instructions and devices to develop your hands-on skills in the following topics: Using built-in conversion functions, Working with built-in logical functions, Using functions that work with NULL.

Join over 3 million cybersecurity professionals advancing their career
Sign up with
or

Already have an account? Sign In »

Overview

Introduction

The Working with Built-in Functions module provides you with the instructions and devices to develop your hands-on skills in the following topics.

  • Using built-in conversion functions
  • Working with built-in logical functions
  • Using functions that work with NULL
  • Lab time: It will take approximately 35 minutes to complete this lab.

Exam Objectives

The following exam objectives are covered in this lab:

  • Demonstrate the use of built-in conversion functions (CAST, CONVERT, and PARSE)
  • Demonstrate the use of built-in logical functions (CHOOSE and IIF) and use functions that work with NULL (ISNULL and COALESCE)

Exercise 1 - Using Built-in Conversion Functions

Transact-SQL provides various conversion functions that support data type casting and conversion. Data typecasting refers to the process where a variable of one particular data type is cast to a different data type. Conversion refers to the process of converting the display format of a variable. Some of the built-in conversion functions are as follows:

  • CAST(): It is used to cast or temporarily convert a variable of one data type into another data type.

Syntax: CAST(expression AS datatype )

  • CONVERT(): It is used to convert the date or time expressions into different formats.

Syntax: CONVERT(datatype, expression [, style] )

  • PARSE(): It returns an expression in the requested data type.

Syntax: PARSE (stringvalue AS datatype [ USING culture ] )

In this exercise, you will learn to use built-in conversion functions.

Exercise 2 - Working with Built-in Logical Functions

Transact-SQL offers various built-in logical functions. Some of the logical functions are as follows:

  • CHOOSE: It returns the name of the item that is specified in the index from the list of items provided.

Syntax: CHOOSE ( index, val1, val2 [, val_n ] )

  • IIF: It returns one of the two values depending on the condition specified. The condition evaluates to either TRUE or FALSE.

Syntax: IIF ( booleanexpression, truevalue, false_value )

In this exercise, you will learn to use these functions in Transact-SQL.

Exercise 3 - Using Functions that Work with NULL

Transact-SQL offers functions and expressions that can work with NULL. They are as follows:

  • COALESCE: It is an SQL expression that checks the arguments specified in the order. It returns the current value of the first argument that is not null
  • ISNULL(): It is a function that replaces NULL value with the specified value

In this exercise, you will learn to use functions that work with NULL.

Learning Partner
Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.