SQL concat : Join Strings of Data

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_WORDSECOND_WORD
rainbow
keyword
format
upcoming
upgrade
daytime
pancake
teamwork
raincoat
blueberry
sailboat
teaspoon
skylark
sandlot
coffeemaker
foreword

 

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_WORDSECOND_WORDCOMPOUND_WORD
rainbowrainbow
keywordkeyword
formatformat
upcomingupcoming
upgradeupgrade
daytimedaytime
pancakepancake
teamworkteamwork
raincoatraincoat
blueberryblueberry
sailboatsailboat
teaspoonteaspoon
skylarkskylark
sandlotsandlot
coffeemakercoffeemaker
forewordforeword

 

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_WORDSECOND_WORDCOMPOUND_WORD
blueberryblueberry
coffeemakercoffeemaker
daytimedaytime
formatformat
forewordforeword
keywordkeyword
pancakepancake
raincoatraincoat
rainbowrainbow
sailboatsailboat
sandlotsandlot
skylarkskylark
teaspoonteaspoon
teamworkteamwork
upgradeupgrade
upcomingupcoming

 

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_PREFIRST_NAMELAST_NAME
Mr.EdwardMatthews
Mr.TimothyBurton
Ms.ChristinaSmithson
Mrs.FrancineThomas
Mr.CharlesRobertson

 

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_PREFIRST_NAMELAST_NAMEMAILING_LABEL
Mr.EdwardMatthewsMr. Edward Matthews
Mr.TimothyBurtonMr. Timothy Burton
Ms.ChristinaSmithsonMs. Christina Smithson
Mrs.FrancineThomasMrs. Francine Thomas
Mr.CharlesRobertsonMr. 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_PREFIRST_NAMELAST_NAME
Mr.EdwardMatthews
Mr.TimothyBurton
Ms.ChristinaSmithson
Mr.EdwardMatthews
Mr.JacobBurton
Ms.EmilyAdams
(null)HaydenWilliams
Mr.CharlesRobertson
Mrs.FrancineThomas
Mrs.AvaBell
Mr.EdwardMatthews
Mr.RobertHoward
Mrs.BellaKelly
(null)LiamBailey
(null)LaurenMorgan

 

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_PREFIRST_NAMELAST_NAMEMAILING_LABEL
Mr.EdwardMatthewsMr.EdwardMatthews
Mr.TimothyBurtonMr.TimothyBurton
Ms.ChristinaSmithsonMs.ChristinaSmithson
Mr.EdwardMatthewsMr.EdwardMatthews
Mr.JacobBurtonMr.JacobBurton
Ms.EmilyAdamsMs.EmilyAdams
(null)HaydenWilliams(null)
Mr.CharlesRobertsonMr.CharlesRobertson
Mrs.FrancineThomasMrs.FrancineThomas
Mrs.AvaBellMrs.AvaBell
Mr.EdwardMatthewsMr.EdwardMatthews
Mr.RobertHowardMr.RobertHoward
Mrs.BellaKellyMrs.BellaKelly
(null)LiamBailey(null)
(null)LaurenMorgan(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_PREFIRST_NAMELAST_NAMEMAILING_LABEL
Mr.EdwardMatthewsMr. Edward Matthews
Mr.TimothyBurtonMr. Timothy Burton
Ms.ChristinaSmithsonMs. Christina Smithson
Mr.EdwardMatthewsMr. Edward Matthews
Mr.JacobBurtonMr. Jacob Burton
Ms.EmilyAdamsMs. Emily Adams
(null)HaydenWilliamsHayden Williams
Mr.CharlesRobertsonMr. Charles Robertson
Mrs.FrancineThomasMrs. Francine Thomas
Mrs.AvaBellMrs. Ava Bell
Mr.EdwardMatthewsMr. Edward Matthews
Mr.RobertHowardMr. Robert Howard
Mrs.BellaKellyMrs. Bella Kelly
(null)LiamBaileyLiam Bailey
(null)LaurenMorganLauren 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_FIRSTSTUDENT_LASTCLASS_ID
CharlieWhite110
CharlieWhite220
ThomasHenderson110
ThomasHenderson220
ThomasHenderson330
CandaceWinter330
VictorWallace110
VictorWallace330
AimeeForest220
AimeeForest330
SeanTaylor330
ShannonGriffin110
ShannonGriffin220
WilliamRoss110
GrahamGardner110
KennethGordon220

 

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_LASTSTUDENT_FIRSTGROUP_CONCAT(CLASS_ID)
ForestAimee220,330
GardnerGraham110
GordonKenneth220
GriffinShannon220,110
HendersonThomas330,220,110
RossWilliam110
TaylorSean330
WallaceVictor330,110
WhiteCharlie220,110
WinterCandace330

 

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