Blog

Concatenate in SQL

If you are looking to learn sql concatie concatenate sql results or columns, texts, or values ​​in general to get a string from the array, you are looking for the SQL CONCAT function.

Below you can see a brief explanation, examples of use and some keys for its full comprehension.

Content

  • SQL function to concatenate
    • CONCAT SQL and SELECT example
    • Example of CONCAT SQL on UPDATE
    • WHERE with CONCAT

SQL function to concatenate

The CONCAT function allows you to concatenate data without limit, so that the result will always be a text string with all the values ​​entered.

The SQL CONCAT function has several restrictions and behaviors that are good to be aware of:

  1. Requires at least 2 values ​​to concatenate, otherwise an error will occur.
  2. There is no limit when entering values ​​and columns, so you can join from 2 values ​​up to 200 or as many as you need.
  3. The function transforms entered values/columns into strings before performing the join.
  4. NULL values ​​are converted to empty strings.
  5. If CONCAT receives NULL values ​​in all its arguments, it will return an empty string of type VARCHAR(1).

Now that you know how it is used and its descriptions, I am going to show you several examples for clarity.

Now we go with the result for your search “concat sql examples”.

CONCAT SQL and SELECT example

The following query returns in 1 column the data of the name and surname of each row of the table citizen.

SELECT CONCAT( nombre, apellidos ) as nombre_completo
FROM ciudadano;

This example has a problem, and it is that when concatenate the 2 columns the result will be the first and last name without being separated by a blank space, which will be quite ugly and not very usable.

To achieve leaving a white space in between I can concatenate it too:

SELECT CONCAT( nombre, ' ', apellidos ) as nombre_completo
FROM ciudadano;

Ready! I already have a query that returns the full name of all the records in the table citizen.

Example of CONCAT SQL on UPDATE

The following example updates a column full name with the concatenation of the first and last names columns of each row:

UPDATE ciudadano
SET nombre_completo = CONCAT( nombre, ' ', apellidos);

The SQL CONCAT function It could also be used to perform a certain condition in a WHERE.

WHERE with CONCAT

The following query with CONCAT allows me to filter all those rows that have citizens whose full name and surname are Jorge López or Roberto López

SELECT id, nombre, dni, direccion
FROM ciudadano
WHERE CONCAT(nombre, ' ', apellidos) = 'Jorge López' OR CONCAT(nombre, ' ', apellidos) = 'Roberto López';

Leave a Reply