Functions

a subroutine that performs a specific task and returns a value

Functions are typically used to perform calculations or transformations on data, and can be called from within a SQL query.

There are two main types of functions in SQL:

1. Scalar functions: These functions take one or more input values and return a single scalar value (i.e., a value of a specific data type, such as a number or string). For example, the LEN function in SQL Server returns the length of a string.

2. Table-valued functions: These functions take one or more input values and return a table as their output. This allows them to be used as a data source in a SQL query, just like a table. For example, the ROWS function in SQL Server returns a table containing a specified number of rows with default values.

Usage

Functions can be used in a variety of ways in SQL, including in the SELECT, WHERE, and GROUP BY clauses of a query. They can also be used in the HAVING clause to filter the results of a GROUP BY operation, or in the ORDER BY clause to sort the results of a query.

DATE_TRUNC: SQL Timestamp Function Explained | Mode

Data Cleaning Functions

• CAST: Converts a value of any type into a specific, different data type

CAST(expression AS datatype)

• POSITION: Returns the position of the first occurrence of a substring in a string

POSITION(substring IN string)

• STRPOS: Converts a value of any type into a specific, different data type

STRPOS(string, substring)

Position/Strpos: Used to return the position of information to identify where relevant information is held in a string to then extract across all records

• COALESCE: Returns the first non-null value in a list.

COALESCE(val1, val2, val3)

Coalesce: Used to return the first non-null value that’s commonly used for normalizing data that’s stretched across multiple columns and includes NULLs.