When we want to combine columns of text data types, we concatenate the string or link a series of values into a single string. Databases use different syntax for this concatenation and we will review both types.

We want to create a single sentence out of some of the data we have—let’s try something like ‘Odie is a Yellow dog and he lives with Jon Arbuckle.’ We have the NameOfDog, ColorOfDog and OwnerName in our database. What we don’t have are the fragments ‘is a’ and ‘dog who lives with‘, so we will have to add them to our query. We can’t forget the punctuation either—this will give us a total of 6 components in our where clause.

In SQL Server and MS Access, we will use the + sign and in the other RDBMS we will use the || sign. Let’s try it out.

[sql]SELECT NameOfDog + ‘ is a ‘ + ColorOfDog + ‘ dog who lives with ‘ + OwnerName + ‘.’
FROM Dog
INNER JOIN PetOwner ON PetOwner.OwnerIdentifier = Dog.OwnerIdentifier
WHERE Dog.DogIdentifier < 510;[/sql]

 

[sql]SELECT NameOfDog || ‘ is a ‘ || ColorOfDog || ‘ dog who lives with ‘ || OwnerName || ‘.’
FROM Dog
INNER JOIN PetOwner ON PetOwner.OwnerIdentifier = Dog.OwnerIdentifier
WHERE Dog.DogIdentifier < 510;[/sql]

 

OUTPUT

SQLSentence

—————————————————–
Odie    is a Yellow dog who lives with Jon Arbuckle.

Laddie  is a Brown dog who lives with Sophia.

Jack    is a Tan dog who lives with Robert.

Ace    is a White dog who lives with Ava.

Ace    is a Gray dog who lives with David Leal.

Jack    is a Black and White dog who lives with Zoe.

Jack    is a Black dog who lives with Joseph Abramson.

Jack    is a White and Yellow dog who lives with Madelyn.

Pluto  is a Yellow Orange dog who lives with Mickey Mouse.

Hercules is a Yellow dog who lives with Charlotte.

(10 row(s) affected)

You will notice each of the text components we added start and end in a single quote. Every time we want to add another part to the string, we use the + or || signs to denote we are continuing our string. Also notice there are no commas between the column names. This indicates we want all of the information to be returned in a single column. We can also add other columns to the final result of the query. Let’s say we wanted to see each column we used above plus the full sentence. Our query would look like this.

[sql]SELECT NameOfDog, ColorOfDog, OwnerName,
NameOfDog + ‘ is a ‘ + ColorOfDog + ‘ dog who lives with ‘ + OwnerName + ‘.’
FROM Dog
INNER JOIN PetOwner ON PetOwner.OwnerIdentifier = Dog.OwnerIdentifier
WHERE Dog.DogIdentifier < 510;[/sql]

 

OUTPUT (Modified)
NameOfDog ColorOfDog OwnerName SQLSentence
——— ———– ———– ———
Odie Yellow Jon Arbuckle Odie is a Yellow dog who lives with Jon Arbuckle.
Laddie Brown Sophia Laddie is a Brown dog who lives with Sophia.
Jack Tan Robert Jack is a Tan dog who lives with Robert.
Ace White Ava Ace is a White dog who lives with Ava.
Ace Gray David Leal Ace is a Gray dog who lives with David Leal.
Jack Black and White Zoe Jack is a Black and White dog who lives with Zoe.
Jack Black Joseph Abramson Jack is a Black dog who lives with Joseph Abramson.
Jack White and Yellow Madelyn Jack is a White and Yellow dog who lives with Madelyn.
Pluto Yellow Orange Mickey Mouse Pluto is a Yellow Orange dog who lives with Mickey Mouse.
Hercules Yellow Charlotte Hercules is a Yellow dog who lives with Charlotte.
(10 row(s) affected)

 

Why is there white space between the name of the dog and the rest of the sentence?

Great Question! The white space exists because the column NameOfDog in the dog table has a data type of CHAR(40). This means the column will be 40 characters wide. If a record does not contain forty characters, like Odie, the rest of the record will be filled with white space.

I

Want to follow along on your own database?

Do you want to follow along with the examples?  We have created a sample database you can use to follow along and try in your own database.  Go to the download page and follow the instructions.  Have fun on the SQL trail!