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.
SELECT NameOfDog + ' is a ' + ColorOfDog + ' dog who lives with ' + OwnerName + '.' FROM Dog INNER JOIN PetOwner ON PetOwner.OwnerIdentifier = Dog.OwnerIdentifier WHERE Dog.DogIdentifier &amp;lt; 510;
SELECT NameOfDog || ' is a ' || ColorOfDog || ' dog who lives with ' || OwnerName || '.' FROM Dog INNER JOIN PetOwner ON PetOwner.OwnerIdentifier = Dog.OwnerIdentifier WHERE Dog.DogIdentifier &lt; 510;
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.
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 &lt; 510;
|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.
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!