> For the complete documentation index, see [llms.txt](https://dailyjournal.gitbook.io/notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dailyjournal.gitbook.io/notes/languages/sql/functions.md).

# 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.

{% embed url="<https://mode.com/blog/date-trunc-sql-timestamp-function-count-on/>" %}

#### Data Cleaning Functions

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

```sql
CAST(expression AS datatype)
```

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

```sql
POSITION(substring IN string)
```

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

```sql
STRPOS(string, substring)
```

{% hint style="info" %}
**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
{% endhint %}

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

```sql
COALESCE(val1, val2, val3)
```

{% hint style="info" %}
**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.
{% endhint %}