Udemy logo

sql concatIntroduction 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;

FIRST_WORD SECOND_WORD
rain bow
key word
for mat
up coming
up grade
day time
pan cake
team work
rain coat
blue berry
sail boat
tea spoon
sky lark
sand lot
coffee maker
fore word

 

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:

FIRST_WORD SECOND_WORD COMPOUND_WORD
rain bow rainbow
key word keyword
for mat format
up coming upcoming
up grade upgrade
day time daytime
pan cake pancake
team work teamwork
rain coat raincoat
blue berry blueberry
sail boat sailboat
tea spoon teaspoon
sky lark skylark
sand lot sandlot
coffee maker coffeemaker
fore word foreword

 

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

 

FIRST_WORD SECOND_WORD COMPOUND_WORD
blue berry blueberry
coffee maker coffeemaker
day time daytime
for mat format
fore word foreword
key word keyword
pan cake pancake
rain coat raincoat
rain bow rainbow
sail boat sailboat
sand lot sandlot
sky lark skylark
tea spoon teaspoon
team work teamwork
up grade upgrade
up coming upcoming

 

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;

TITLE_PRE FIRST_NAME LAST_NAME
Mr. Edward Matthews
Mr. Timothy Burton
Ms. Christina Smithson
Mrs. Francine Thomas
Mr. Charles Robertson

 

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:

TITLE_PRE FIRST_NAME LAST_NAME MAILING_LABEL
Mr. Edward Matthews Mr. Edward Matthews
Mr. Timothy Burton Mr. Timothy Burton
Ms. Christina Smithson Ms. Christina Smithson
Mrs. Francine Thomas Mrs. Francine Thomas
Mr. Charles Robertson Mr. Charles Robertson

 

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;
TITLE_PRE FIRST_NAME LAST_NAME
Mr. Edward Matthews
Mr. Timothy Burton
Ms. Christina Smithson
Mr. Edward Matthews
Mr. Jacob Burton
Ms. Emily Adams
(null) Hayden Williams
Mr. Charles Robertson
Mrs. Francine Thomas
Mrs. Ava Bell
Mr. Edward Matthews
Mr. Robert Howard
Mrs. Bella Kelly
(null) Liam Bailey
(null) Lauren Morgan

 

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:

 

TITLE_PRE FIRST_NAME LAST_NAME MAILING_LABEL
Mr. Edward Matthews Mr.EdwardMatthews
Mr. Timothy Burton Mr.TimothyBurton
Ms. Christina Smithson Ms.ChristinaSmithson
Mr. Edward Matthews Mr.EdwardMatthews
Mr. Jacob Burton Mr.JacobBurton
Ms. Emily Adams Ms.EmilyAdams
(null) Hayden Williams (null)
Mr. Charles Robertson Mr.CharlesRobertson
Mrs. Francine Thomas Mrs.FrancineThomas
Mrs. Ava Bell Mrs.AvaBell
Mr. Edward Matthews Mr.EdwardMatthews
Mr. Robert Howard Mr.RobertHoward
Mrs. Bella Kelly Mrs.BellaKelly
(null) Liam Bailey (null)
(null) Lauren Morgan (null)

 

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

 

TITLE_PRE FIRST_NAME LAST_NAME MAILING_LABEL
Mr. Edward Matthews Mr. Edward Matthews
Mr. Timothy Burton Mr. Timothy Burton
Ms. Christina Smithson Ms. Christina Smithson
Mr. Edward Matthews Mr. Edward Matthews
Mr. Jacob Burton Mr. Jacob Burton
Ms. Emily Adams Ms. Emily Adams
(null) Hayden Williams Hayden Williams
Mr. Charles Robertson Mr. Charles Robertson
Mrs. Francine Thomas Mrs. Francine Thomas
Mrs. Ava Bell Mrs. Ava Bell
Mr. Edward Matthews Mr. Edward Matthews
Mr. Robert Howard Mr. Robert Howard
Mrs. Bella Kelly Mrs. Bella Kelly
(null) Liam Bailey Liam Bailey
(null) Lauren Morgan Lauren Morgan

 

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;

 

STUDENT_FIRST STUDENT_LAST CLASS_ID
Charlie White 110
Charlie White 220
Thomas Henderson 110
Thomas Henderson 220
Thomas Henderson 330
Candace Winter 330
Victor Wallace 110
Victor Wallace 330
Aimee Forest 220
Aimee Forest 330
Sean Taylor 330
Shannon Griffin 110
Shannon Griffin 220
William Ross 110
Graham Gardner 110
Kenneth Gordon 220

 

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:

 

STUDENT_LAST STUDENT_FIRST GROUP_CONCAT(CLASS_ID)
Forest Aimee 220,330
Gardner Graham 110
Gordon Kenneth 220
Griffin Shannon 220,110
Henderson Thomas 330,220,110
Ross William 110
Taylor Sean 330
Wallace Victor 330,110
White Charlie 220,110
Winter Candace 330

 

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

Page Last Updated: May 2014

Top courses in SQL

Microsoft SQL for Beginners
Mubashir Hassan | worldwide 80000+ enrollments
4.6 (54)
New
The SQL Programming Essentials 2021 Immersive Training
SDE OCTOPUS, Ahmed Ibrahim
4.5 (40)
New
The Complete SQL Bootcamp 2021: Go from Zero to Hero
Jose Portilla
4.7 (109,610)
Bestseller
The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
Colt Steele, Ian Schoonover
4.6 (58,918)
Bestseller
Master SQL For Data Science
Imtiaz Ahmad
4.7 (8,397)
SQL for Beginners: Learn SQL using MySQL and Database Design
Tim Buchalka's Learn Programming Academy, Jon Avis - SQL Instructor
4.5 (15,704)
Learn SQL +Security(pen) testing from Scratch
Rahul Shetty
4.5 (1,806)
Bestseller

More SQL Courses

SQL students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo