Data and Business Intelligence Glossary Terms

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
V
W
X

Concat

Also known as

concatenate

What is concat?

Concat (or concatenate) is a function that links different units of data together to treat it as one unit of data. The word comes from the Latin root con- for “together”, and caten- for “chain”. For example, you can concatenate the name of a city with the name of a state to make a linked unit of information called a location.

In most BI tools, you can assume that the concat function only works with the text data type (often called a string). If you want to use concat with other data types, like numbers or dates, you’ll have to convert them to text first.

How to use concat

The following examples show you how concat can be used to link together city and state values from two different columns, so they can be treated as a single location in a new column.

Concat in a spreadsheet

In a spreadsheet app like Excel or Google Sheets, you’ll usually write the concat (or concatenate) formula directly into a spreadsheet cell using cell references, and “fill down” that column with your formula. You can also include other pieces of text inside quotation marks. For example:

  • Cell A1: “San Francisco”
  • Cell B1: “California”
  • Cell C1: =concat(A1, ", ", B1) → “San Francisco, California”

Note that the use of ", " in the concat formula to add a space and comma between the city and the state.

Concat in Metabase

In Metabase, you can write a custom expression for concat inside the field formula for a custom column in the notebook editor.

For example, you can use the expression concat([City], ", ", [State]) to add a new column for Location in the People table that comes with the Metabase sample database.

Concat in SQL

If you’re writing SQL, you can include the CONCAT function inside a SELECT statement:

SELECT
    CONCAT(City, ", ", State) AS "Location"
FROM
    People;

The Metabase concat expression is translated into a SQL CONCAT function before running against your database.

Note that the SQL functions JOIN, UNION, and MERGE might sound like they work similarly to CONCAT, but they are used to combine rows and columns from different tables, rather than text strings from different columns.

Related terms

Further reading

Thanks for your feedback!