> ## Documentation Index
> Fetch the complete documentation index at: https://docs.prophecy.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Functions

> Build functions with SQL macros to be used in gem expressions

In SQL projects, functions are SQL macros that transform data at the column level. Unlike gems, which operate at the table level, functions apply transformations to individual columns, making them useful for data cleansing, formatting, and complex calculations. Functions are compiled into code using the Jinja templating language (standard for dbt macros).

You will use functions as [expressions](/data-analysis/gems/visual-expression-builder/visual-expression-builder) in gems.

## Create a function

To add a new function to your project, perform the following steps.

1. Open a SQL project.
2. Click **Add Entity** in the project sidebar.
3. Select **Function**.
4. Name the function.
5. Keep the function in the `macros` directory.
6. Click **Create**.

This opens the function configuration screen.

## Build the function

You can build functions visually by populating the following fields.

| Field       | Description                                                                                              |
| ----------- | -------------------------------------------------------------------------------------------------------- |
| Description | A summary of what the function will do.                                                                  |
| Parameters  | The parameters (arguments) that will be passed to the function. Parameters can be values or table names. |
| Definition  | SQL code that will be executed by the function.                                                          |

## Example: Concatenate columns

Use the following example to learn how to build a function and use it in your pipeline. This example demonstrates a function that concatenates values from a `first_name` and `last_name` column in a customer table.

1. Click **+Add Entity** in the project sidebar.
2. Select **Function**.
3. Name the function `concat_name`.
4. Click **Create**.
5. Add the following description: `Concatenates customer first and last names in a new column`.
6. Add two parameters to the function: `first_name` and `last_name`.
7. Add the following to the macro body

```jinja theme={null}
CONCAT(
    UPPER(LEFT({{ first_name }}, 1)),
    LOWER(SUBSTRING({{ first_name }}, 2)),
    ' ',
    UPPER(LEFT({{ last_name }}, 1)),
    LOWER(SUBSTRING({{ last_name }}, 2))
)
```

To use this function in your pipeline:

1. Add a Reformat gem to the pipeline canvas.
2. For the target column, create a new column named `full_name`.
3. For the expression, select **Function > concat\_name**.
4. For the function parameters, choose a first name columns and a last name column.
5. Save and run the gem.

In summary, this function inside of the Reformat gem let us combine customer first and last names into a new column: `full_name`.