Menu
  • Text Function
    • CONCAT()
    • CHAR_LENGTH()
    • CHARACTER_LENGTH()
    • BYTE_LENGTH()
    • UPPER()
    • LOWER()
    • INITCAP
    • SUBSTR()
    • SUBSTRING()
    • REPLACE()
    • TRIM()
    • REVERSE()
    • STRPOS()
    • SPLIT()
  • Operators
    • PIVOT
  • Drivers
    • ODBC Driver for SQLite
August 1, 2022September 14, 2022

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:

customershipping_addressfull_address_concatfull_address_operator
Jones & Son, Inc.10-2 Parkson St, Boston, MA 02136, USAJones & Son, Inc. , 10-2 Parkson St, Boston, MA 02136, USAJones & Son, Inc. , 10-2 Parkson St, Boston, MA 02136, USA
Jones & Son, Inc.10-2 Parkson St, Boston, MA 02136, USAJones & Son, Inc. , 10-2 Parkson St, Boston, MA 02136, USAJones & Son, Inc. , 10-2 Parkson St, Boston, MA 02136, USA
Stavros GmbHLandsberger Alee 49, 10249, Berlin, GermanyStavros GmbH, Landsberger Alee 49, 10249, Berlin, GermanyStavros GmbH, Landsberger Alee 49, 10249, Berlin, Germany
Hendricks, Hendricks, HoekstraAnna Paulownastraat, 3014 JA, Rotterdam, NetherlandsHendricks, Hendricks, Hoekstra, Anna Paulownastraat, 3014 JA, Rotterdam, NetherlandsHendricks, Hendricks, Hoekstra, Anna Paulownastraat, 3014 JA, Rotterdam, Netherlands
Mckenna & Partners2-8 Ingram St, Glasgow G1 1HA, UKMckenna & Partners, 2-8 Ingram St, Glasgow G1 1HA, UKMckenna & Partners, 2-8 Ingram St, Glasgow G1 1HA, UK

Articles

  • ODBC Driver for SQLite
  • SQL Server PIVOT operator
  • Text Function – SPLIT()
  • Text Function – TRIM() , REVERSE() and STRPOS()
  • Text Function – SUBSTR(), SUBSTRING() and REPLACE()
©2023