Text Function – CONCAT()

It stitches together two or more text values into one string. The most famous function for this purpose is CONCAT(); the || operator stitches the strings together. Let’s examine how each of these functions works as an example:

SELECT customer,
       shipping_address,
       CONCAT(customer, ', ', shipping_address) AS full_address_concat,
       customer|| ', ' || shipping_address AS full_address_operator
FROM `orders.orders`;

• Inputs to the function or the operator must all be BYTES or a data type that can be converted to a STRING. A date or a float will be converted to a string in the output, but it is unnecessary to cast it first
• Either BYTES or a STRING is the function’s return
• The function will return the NULL value as the output

The first thing I do is select the customer, and then I select their address. I want to list both the item’s name and the address in the same row. In order to achieve this, I used the CONCAT() function, and the two columns I needed were customer and shipping_address.

We need the `name` and the `address` to be separated by a comma and a blank space after it, so we need to use the single quote, not double quotes here.

The operator works the same way. The logic is that you put a || between the strings to glue them together. The method does not call for a specific function and the “arguments” are separated by a ||.

Here’s the code that was written:

customer	shipping_address	full_address_concat	full_address_operator
Jones & Son, Inc.	10-2 Parkson St, Boston, MA 02136, USA	Jones & Son, Inc. , 10-2 Parkson St, Boston, MA 02136, USA	Jones & Son, Inc. , 10-2 Parkson St, Boston, MA 02136, USA
Jones & Son, Inc.	10-2 Parkson St, Boston, MA 02136, USA	Jones & Son, Inc. , 10-2 Parkson St, Boston, MA 02136, USA	Jones & Son, Inc. , 10-2 Parkson St, Boston, MA 02136, USA
Stavros GmbH	Landsberger Alee 49, 10249, Berlin, Germany	Stavros GmbH, Landsberger Alee 49, 10249, Berlin, Germany	Stavros GmbH, Landsberger Alee 49, 10249, Berlin, Germany
Hendricks, Hendricks, Hoekstra	Anna Paulownastraat, 3014 JA, Rotterdam, Netherlands	Hendricks, Hendricks, Hoekstra, Anna Paulownastraat, 3014 JA, Rotterdam, Netherlands	Hendricks, Hendricks, Hoekstra, Anna Paulownastraat, 3014 JA, Rotterdam, Netherlands
Mckenna & Partners	2-8 Ingram St, Glasgow G1 1HA, UK	Mckenna & Partners, 2-8 Ingram St, Glasgow G1 1HA, UK	Mckenna & Partners, 2-8 Ingram St, Glasgow G1 1HA, UK