SQL Advanced Functions

mayuri budake
Nov 4 · 3 min read
  1. CAST() Function:
  • It is the Conversions Function used to convert data from one data type to another.

Syntax:

CAST(expression AS datatype(length))

Description:

  • Expression - It is required for convert value.
  • Datatype - It is required to convert expression to specific datatype.
  • (Length) - It is optional. The length of the resulting data type.

Example:

We use this table .

1) CAST function convert the CustScore column from type Float to Integer.

Float to Integer Conversion.

2)CAST function convert the CustScore column from type Float to Decimal.

Float to Decimal Conversion.

3)CAST function convert the CustDOB column from type Date to DateTime.

Date to DateTime Conversion.

2. COALESCE() function:

  • This function returns the first non-null value from the argument list.
  • If all expressions evaluate to null, then the COALESCE function will return null.
  • This function does not limit the number of arguments, but they must all of the same data type.

Syntax:

COALESCE(expression 1, … expression n)

Description:

  • expression 1 to expression_n - Expressions is required to test non-null values.

Example:

COALESCE() Examples.
  • We use CustomerInfo table.
CustomerInfo Table.
It display first occurrence argument from table.

3. CONVERT() Function:

  • It is the Conversions Function used to convert data from one data type to another.
  • If the conversion fails, the function return an error.

Syntax:

CONVERT(data_type(length), expression, style)

Description:

  • Datatype - It is required to convert expression to specific datatype.
  • (Length) - It is optional. The length of the resulting data type.
  • Expression - It is required for convert value.
  • Style - It is an optional integer.

It determines how the convert() function will translate expression.

If style is NULL, the Convert() function will return NULL.

Example:

Simple Convert() Examples.
Convert() Example with Date Style.

If you are new to SQL refer Below video for better Understanding.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade