Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Adding Text Columns Together3:39 with Andrew Chalkley
You are not restricted by the column definitions in the database schema. You can join columns together so they're more human readable as one.
SQLite, PostgreSQL and Oracle
Use the concatenation operator
SELECT <value or column> || <value or column> || <value or column> FROM <table>;
Use the concatenation operator
SELECT <value or column> + <value or column> + <value or column> FROM <table>;
MySQL, Postgres and MS SQL
SELECT CONCAT(<value or column>, <value or column>, <value or column>) FROM <table>;
You're not restricted by the column definitions in the database schema. 0:00 Remember in a previous course, 0:04 we used the AS keyword to alias the column names to be more human readable. 0:06 Take this query for example. 0:12 We see that the first name, last name, 0:14 email, and phone all have human readable aliases. 0:19 Let's run this query. 0:27 This generates a report with two columns, the first name and the last name 0:30 that could be joined together to save a little bit of visual clutter. 0:36 But how do we join these two name columns together? 0:40 We can use an operator to join two pieces of text from two columns, 0:44 called the concatenation operator. 0:49 Concatenate means to link or join things together. 0:52 In this case, it's two strings of text. 0:56 The concatenation operator in SQL Playgrounds 0:59 is two pipes of vertical line characters. 1:03 That's how you combine two strings in SQLite. 1:06 You can type the pipe character on a US keyboard by holding down the Shift and 1:10 backslash key. 1:15 In other SQL environments, you may need to use a different set of characters for 1:17 the concatenation operator, or sometimes it's even a function. 1:21 I'll include details in the teacher's notes. 1:26 You can use the concatenation operator to join two text values together 1:29 in the following way. 1:34 First, you specify the value or column, 1:36 the concatenation operator, and then the second value or column. 1:40 In fact, you can join any number of values or columns together. 1:45 You can even alias the newly joined value to have an appropriate name. 1:50 Let's jump back to our customary example and join the first name and last name. 1:55 Let's replace the first name alias and the comma here with the concatenate operator. 2:01 Let's also change the alias from last name to full name, 2:09 since that's what we're doing. 2:13 Let's run this. 2:15 As you can see, the first name and last names are now joined. 2:17 But there's no space between the first name and the last name. 2:22 That's because we didn't specify it. 2:26 SQL, like other programming languages, require precise instructions. 2:29 Computers don't understand our intentions without us explicitly telling them 2:33 what we want them to do. 2:37 We need to join another piece of text in the middle, the value of the space. 2:39 Remember, we need to add quote marks around the space character for 2:47 the database to recognize it as a string of text. 2:51 When we run it now, it correctly shows the full name with a space in the middle. 2:57 Performing concatenation doesn't affect the data in the database, 3:03 it just presents it differently. 3:07 So you could still add WHERE conditions and 3:09 orderings based on the last name, and it should still work as you expect. 3:13 Now, you can join the values from columns together and 3:27 insert strings of text between them too. 3:30 Remember to check the teacher's notes to see the different ways 3:33 you can concatenate strings in SQL databases. 3:36
You need to sign up for Treehouse in order to download course files.Sign up