Working with Built-in Functions
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.
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.
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 (string_value AS data_type [ 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, val_1, val_2 [, 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 ( boolean_expression, true_value, 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.
IT & Cybersecurity certification hands on practice labs and practice exams for certifications and skill development.
See the full benefits of our immersive learning experience with interactive courses and guided career paths.