SQL String Concatenation [5 Methods]

In the rapidly evolving world of data management and analysis, SQL has proven to be a versatile and powerful language for handling and manipulating structured data. One of the core features of SQL that makes it indispensable for data professionals is string concatenation - the art of combining data from multiple columns into a single, unified output. As simple as it may sound, string concatenation is a critical skill that can unlock a new level of efficiency and insight in your data-driven tasks.

In this comprehensive article, we will delve into the world of SQL string concatenation, exploring its fundamentals, various techniques, and best practices to help you master the craft of merging data. Whether you are a beginner looking to expand your SQL knowledge or an experienced data analyst seeking to refine your skills, this article will equip you with the know-how to optimize your database queries and transform raw data into meaningful information. So, without further ado, let's embark on this exciting journey to unveil the true potential of SQL string concatenation.

 

Overview of SQL string datatype

In SQL, a string is a sequence of characters used to represent textual data. String data types are used to store and manipulate text data in a database. There are different string data types available in SQL, depending on the database management system (DBMS) being used. The most common string data types include:

1. CHAR (n): This data type represents a fixed-length character string, where "n" specifies the number of characters. When the string stored is shorter than the defined length, the remaining space is filled with padding characters, usually spaces. CHAR is most suitable for storing strings with a consistent length, such as codes or abbreviations. For example name char(20)
2. VARCHAR (n) or VARCHAR2 (n): This data type represents a variable-length character string, where "n" specifies the maximum number of characters. Unlike CHAR, VARCHAR does not pad the unused space with characters. It only allocates the amount of space required to store the actual string, making it more efficient for storing strings with varying lengths, such as names or addresses. For example: name varchar(100)
3. TEXT or CLOB: These data types are used to store large amounts of text data. TEXT (or CLOB in some databases) can store a significantly larger amount of characters compared to CHAR or VARCHAR, making them ideal for storing long textual content such as articles, comments, or descriptions. For example: name TEXT

 

SQL string concatenation methods

String concatenation is the process of combining two or more strings into a single, unified output. In SQL, different databases offer various methods for string concatenation. Here, we will discuss some of the most common methods used across different database management systems (DBMS):

1. CONCAT function: Standard SQL function to combine multiple strings into one, supported by MySQL, SQL Server, Oracle, and PostgreSQL.

   <span class="hljs-function">SELECT <span class="hljs-title">CONCAT</span>(<span class="hljs-params">first_name, <span class="hljs-string">' '</span>, last_name</span>) <span class="hljs-keyword">as</span> full_name FROM employees</span>;
   

2. Double Pipe Operator (||): Concatenates strings using the || operator, supported by Oracle, PostgreSQL, and SQLite.

   SELECT first_name || ' ' || last_name as full_name FROM employees;
   

3. Plus Operator (+): Concatenates strings using the + operator, used in SQL Server and Microsoft Access.

   SELECT first_name + ' ' + last_name as full_name FROM employees;
   

4. CONCAT_WS function: Concatenates strings with a specified separator, available in MySQL and MariaDB.

   SELECT CONCAT_WS(' ', first_name, last_name) as full_name FROM employees;

 

1. SQL CONCAT() Function

The CONCAT() method in SQL is used to combine multiple strings into one string. The CONCAT() function returns a single concatenated string after accepting two or more string parameters as inputs.

Syntax:

CONCAT(STRING1, STRING2, ...)

string1,string2,.. : constant string or column name of string datatype

Here is an example of using the CONCAT() function to concatenate two columns in a SELECT statement. Consider Hospital management database to perform practical examples on SQL CONCAT() function to perform concatenation of two strings

 

SQL string concatenation on columns of single table

Example 1: Write SQL query to combine patient name with the disease in the single string

SELECT    patient_id, { fn CONCAT(name, disease) } AS 'Name&Disease'
FROM    patient

• In the above query, SQL select statement is used to select the patient_id column and concatenates the name and disease columns of the patient table using the CONCAT() function. The result of the concatenation is returned as a new column named 'Name&Disease'.
• The CONCAT() function concatenates the name and disease columns of the patient table. The fn keyword is used to indicate that a function is being called within the SELECT statement.

OUTPUT:

 

SQL string concatenation on columns of multiple tables

Example 2: Write SQL query to combine patient name with doctor name in a single string

SELECT    patient.patient_id, { fn CONCAT(patient.name, doctor.name) } AS 'patient&doctor'
FROM  patient INNER JOIN  doctor ON patient.doctor_id = doctor.doctor_id;

• In the above query, SQL select join query the patient and doctor tables using the doctor_id column as the common field, and selects the patient_id column and a concatenated string of patient.name and doctor.name columns as a new column named 'patient&doctor'.
• The CONCAT() function concatenates the patient.name and doctor.name columns. The fn keyword is used to indicate that a function is being called within the SELECT statement.
• The resulting column 'patient&doctor' will show the concatenated string for each patient and their corresponding doctor.

OUTPUT:

 

SQL string concatenation with nested concat() function

The CONCAT() function is one of the functions in SQL that can be nested inside of other functions. You may alter and conduct more intricate operations on your data by using nesting functions.

Example 3: Write SQL query to do concatenation of patient name with disease also add ‘-‘as a separator

SELECT patient_id, { fn CONCAT(name, { fn CONCAT('-', disease) }) } AS 'Name&Disease'
FROM patient

• In the above query, SQL select query selects the patient_id column and concatenates the name and disease columns of the patient table using the CONCAT() function.
• However, the result of the first CONCAT() function is then passed as an argument to a second CONCAT() function that appends a hyphen (-) character to the end of the concatenated string.
• The resulting concatenated string with the hyphen is returned as a new column named 'Name&Disease'.

OUTPUT:

 

2. SQL ‘+’ Operator

When the SQL ‘+’ operator is used with character or string values, it concatenates the two strings together.

Note that the + operator is not supported by all SQL databases. Some databases, such as Oracle and PostgreSQL, use the || operator for string concatenation instead of +.

Syntax:

SELECT 'String1/column_name1 ' + ‘String2/column_name2' AS expression;

string1/column_name1,.. : constant string or column name of string datatype

Example 4: Write SQL query to combine patient name with the disease in the single string

SELECT patient_id, name + disease AS 'Name&Disease'
FROM  patient

• This SQL query selects the patient_id column and concatenates the name and disease columns of the patient table using the + operator.
• The resulting concatenated string is returned as a new column named 'Name&Disease'.

OUTPUT:

Example 5: Write SQL query to combine patient name with doctor name in a single string

SELECT patient.patient_id, patient.name + doctor.name AS 'patient&doctor'
FROM  patient INNER JOIN doctor ON patient.doctor_id = doctor.doctor_id

• In the above query, SQL join select query is used to select the patient_id column from the patient table and concatenates the name column from the patient table and the name column from the doctor table using the + operator.
• The INNER JOIN clause specifies the join condition between the patient and doctor tables, which is that the doctor_id column in the patient table matches the doctor_id column in the doctor table.
• The resulting column 'patient&doctor' will show the concatenated string for each patient and their corresponding doctor

OUTPUT:

Example 6: Write SQL query to do concatenation of patient name with disease also add ‘-‘as a separator

SELECT patient_id, name + '-' + disease AS 'Name&Disease'
FROM patient

• In the above query, SQL select query selects the patient_id column from the patient table and concatenates the name and disease columns using the + operator and a hyphen ('-') character. The resulting concatenated string is returned as a new column named 'Name&Disease'.
• Both name and disease columns are string data types, the result of the + operator will be a string that combines the name value, a hyphen character, and the disease value for each row in the patient table.

OUTPUT:

 

3. SQL ‘+’ Operator with COALESCE

Example 7: Write SQL query to concate patient name with disease if disease is not null

SELECT   name + '  ' + COALESCE (disease, ' ') AS 'Patient Name & Disease'
FROM   patient;

• In the above query ,SQL select query is used to selects data from the "patient" table and concatenates the "name" column with the "disease" column (if it exists), separated by a space.
• The COALESCE function is used to replace null values in the "disease" column with a single space.
• The resulting concatenated string is returned as a new column, which is given the alias 'Patient Name & Disease'. The plus sign (+) is used for string concatenation in SQL Server.

OUTPUT:

 

4. SQL double pipe (||) operator

Example 8: The double pipe operator (||) is used to concatenate strings in SQL. It is supported by Oracle, PostgreSQL, and SQLite. This operator allows you to combine multiple strings into a single output, which can be useful for formatting and displaying data more effectively.

Syntax:

string1 || string2 || ... || stringN

Consider "employees" table with "first_name," "last_name," "title," and "department" columns:

first_name	last_name	title	department
John	Doe	Software Engineer	Development
Jane	Smith	Project Manager	Management

The double pipe operator (||) is placed between the strings that need to be concatenated. You can concatenate any number of strings, and the operator will join them in the order they appear.

SELECT first_name || ' ' || last_name as full_name FROM employees;

In this example, we concatenate the first name, last name, title, and department of each employee.

SELECT first_name || ' ' || last_name || ' - ' || title || ' (' || department || ')' as employee_info FROM employees;

Output

 

5. CONCAT_WS function

Example 9: Concatenate the first name, last name, title, and department of each employee, using a custom separator.

SELECT CONCAT_WS(' - ', CONCAT(first_name, ' ', last_name), title, CONCAT('(', department, ')')) as employee_info FROM employees;

Output:

 

Summary

String concatenation is an essential skill in SQL, enabling the combination of textual data from multiple columns into a single, unified output. This is useful for formatting and displaying data more effectively. There are several methods for concatenating strings, and the choice depends on the database management system (DBMS) being used. The CONCAT function is a standard SQL function supported by MySQL, SQL Server, Oracle, and PostgreSQL. The double pipe operator (||) is another common method, used in Oracle, PostgreSQL, and SQLite. In SQL Server and Microsoft Access, the plus operator (+) is used for string concatenation. Finally, the CONCAT_WS function, available in MySQL and MariaDB, concatenates strings with a specified separator.

 

References

SQL INNER JOIN
SQL operators
SQL datatypes

 

Read More

SQL String Concat