SQL concat : Join Strings of Data

Introduction to SQL concat

SQL concat can be used to join a string with, at least, an additional string. The end of the one string will be joined (or concatenated) with the beginning of the next string. For example; if one field contains the character string value of ‘text’ and a second field contains the character string value of ‘book’, then the concatenated result will be ‘textbook’. I will focus on the Oracle options for SQL. Options for use of the SQL function, concat, are as follows:

For Oracle (two options):   concat   or | |

For Access (two options):   & or +

Examples of SQL concat usage

Scenario 1:

In your first scenario, you have a teacher who has a table including a list of words that can be compounded to make other words. The table name is Compound_Tbl; the columns included in the table are First_Word and Second_Word. The teacher would like to have the values of the columns in the table concatenated in order to have a list of the words created as the teacher’s goal is to demonstrate the concept of compound words.

SELECT * FROM Compound_Tbl;

To concatenate the values of First_Word and Second_Word; you could select the columns in the table, use concat with the columns that you want to concatenate, and designate a new column in which the concatenated result will be displayed.

SELECT
First_Word,
Second_Word,
CONCAT (First_Word, Second_Word)
AS Compound_Word
FROM Compound_Tbl

Output:

You can also add formatting and ordering options, such as ORDER BY.

SELECT
First_Word,
Second_Word,
CONCAT (First_Word, Second_Word)
AS Compound_Word
FROM Compound_Tbl
ORDER BY First_Word

Scenario 2:

A marketing company that provides direct mail services would like to combine data from a few rows into one row that they can more easily refer to when creating mailing labels for their clients. Your table name will be Address_Tbl and the columns used for client title, first name, and last name will be Title_Pre; First_Name; Last_Name.

SELECT * FROM Address_Tbl;

You will use concat along with the columns that you want to concatenate from and the table that includes the columns.

SELECT
Title_Pre,
First_Name,
Last_Name,
CONCAT (Title_Pre, ' ', First_Name, ' ', Last_Name) AS Mailing_Label
FROM Address_Tbl

Output:

Scenario 3:

In this scenario, you will be using a table from the marketing company that wants to combine the titles, first names, and last names of clients. However, in this case, some of the clients did not wish to provide a title.

SELECT * FROM Address_Tbl;

If you use concat in the same manner that you had used it for the previous example, which had not contained null values, the output will not display the names in fields for the Mailing_Label column as the marketing company would have expected.

SELECT
Title_Pre,
First_Name,
Last_Name,
CONCAT (Title_Pre, ' ', First_Name, ' ', Last_Name) AS Mailing_Label
FROM Address_Tbl

Output:

Note the (null) values in the Mailing_Label column.

One option that you may use is the COALESCE function along with the CONCAT function, as listed below [I had also added SPACE(1) to add a space between title and first name as well as a space between first name and last name]:

SELECT
Title_Pre,
First_Name,
Last_Name,
CONCAT (COALESCE(Title_Pre, ' '), SPACE(1), COALESCE(First_Name, ' '), SPACE(1), COALESCE(Last_Name)) as Mailing_Label
FROM Address_Tbl

Would you like to learn more about working with null values as well as many other options found in SQL? Take a class on Udemy.

Scenario 4:

In your fourth scenario, a school has a table that lists the classes that students are taking. The school would like a “snapshot” version of the records that will include a list of classes, which are distinguished by id numbers, and the respective students taking the classes in order for them to clearly see the classes each student is taking. In this case, you could incorporate a MySQL function called GROUP_CONCAT. Interested in delving into MySQL? Take a Udemy Class.

SELECT * FROM Student_Tbl;

You could use GROUP_CONCAT to combine the values in the Class_Id column as follows:

SELECT Student_Last, Student_First,
GROUP_CONCAT(Class_Id)
FROM Student_Tbl
GROUP BY Student_Last;

Output:

Conclusion

As you may have noticed, there are different reasons why you could find the concat function to be very useful. The reasons that you may have for using specific functions will be dependent on factors such as the version of SQL you are using and the environment; such as marketing, for which data is being stored, retrieved, manipulated, etc. Intrigued about marketing possibilities available with SQL? Explore a class on Udemy