In this video, you’ll learn the different methods to concatenate string values in Oracle,
SQL Server, MySQL, and PostgreSQL. You’ll learn when to use one method over the other.
Let’s get into it.
Just quickly, in case you weren’t aware, concatenation is the process of joining two text
values together. A common example is a person’s first and last names to create a full name.
String concatenation is done slightly differently in each database vendor.
In this video, you’ll see the differences in each vendor.
In Oracle SQL, you can concatenate strings using the double pipe operator or the CONCAT function.
Here’s an example using pipes, or those vertical line characters.
This SELECT query selects a value of John
and a value of Smith. In between, there are two pipe characters.
You’ll notice that there is a space after the word John, inside the quotes.
This is done so that there is a space in the result. If a space is not added,
then the two strings are just combined. This is OK but may not be what you want.
When you run this, you’ll get a result like this.
It has the words John and Smith combined into a single value.
You can use the pipe characters on table columns as well. This example
shows how you would select the firstname and lastname columns and concatenate them.
We can see the output here. There is no space between the two values,
because we didn’t specify one.
However, this is easy to do. We can add a space character inside single quotes
in between the firstname and lastname. We use another set of double pipes, like this.
We’re essentially concatenating three strings here: the first name,
a space character, and a last name.
When we run this query, we see this result. We can see the space between the two names now.
The other way to concatenate values in Oracle is to use the CONCAT function.
Here’s what it looks like.
You supply two strings inside the CONCAT function, and close the brackets.
When you run this query, you see this result. We see the firstname and lastname values.
The main drawback of the CONCAT function is that you can only concatenate two values.
If you want to concatenate more values, such as adding a space character, or a middle name,
then you can do this by surrounding the CONCAT function with another CONCAT function.
Here’s how this can be done. We can see the inner CONCAT has a first name and a space,
and the outer CONCAT will combine the result of this first concat with the last name.
So, which method is better? Double pipes or the CONCAT function?
Personally, I prefer the double pipes method. I find it easier to read,
and it also supports three or more strings being concatenated.
Let’s see how it’s done in other databases.
In SQL Server, concatenation is done with the + symbol. It works in the
same way as the double pipe in Oracle. You can also use the CONCAT function.
To concatenate two string values with the plus symbol, you can select the first string,
then plus, then the second string. In this example, we select John with a space, then plus,
then Smith. We don’t need to select From Dual as that’s an Oracle-specific feature.
If we run the query, this is what we will see. We can see the full name of John Smith.
We can use this on columns in a table by selecting them as well as a plus symbol. For example,
we can select the first name and last name columns as well as a plus to concatenate them.
The result would look like this. We can see the first name and last name values combined,
but there is no space in the middle.
We can add a space by adding a plus, then a space, then another plus in the middle
of the firstname and lastname columns. This should add a space in our results.
We can run this query and see the results, which now include a space between the names.
The other way to concatenate values in SQL Server is to use the CONCAT function.
This works in a similar way to Oracle, where you call the function
and place string values inside it that you want to concatenate.
For example, to concatenate both the firstname and lastname, add them into the function like this.
When you run this query, you see an output that has both of the values concatenated.
What if you want to use three values? The good news is that in SQL Server,
you can use three or more values in the CONCAT function. You can actually use up to 254.
Simply add each value you want to concatenate inside the function, separated by a comma.
In this example, we are concatenating a first name, a space, and a last name.
When we run this query, we can see the names separated by a space, in a single column.
So that’s how you can concatenate values in SQL Server.
In MySQL, you concatenate values using either double pipes or the CONCAT function.
It works in a similar way to other databases. You concatenate two
values by selecting them and placing a double pipe character in the middle.
For example, this query will combine the name John and Smith.
However, to support the double pipe character being used for concatenation,
you need to run either of these commands to change the SQL Mode.
Setting the SQL mode to pipes as concat will allow the use of pipes as concatenation characters.
Or, setting the sql mode to ansi will set a range of parameters, including pipes as concat.
Once you’ve set that option,
we can run the Select query which has pipes, and we see the combined name.
To use the CONCAT function, add each string you want to concatenate inside the CONCAT function.
For example, we can select the first name and last
name inside CONCAT to see these columns concatenated.
When we run the query, this is what we see. There are no spaces between the names,
but we can specify those inside the CONCAT function.
This example includes the firstname column, a space, then a lastname column.
The CONCAT function in MySQL supports multiple strings, just like SQL Server.
We can run this query and see the output here, which is a combined firstname and lastname column.
Finally, the PostgreSQL database allows you
to concatenate text using either the double pipes or the CONCAT function.
It works in a similar way to the other databases we’ve seen.
To concatenate strings, select them and separate them with double pipes.
In this example, we concatenate the words John and Smith.
The result of this query shows the combined name in a single field.
Another way is to use the CONCAT function. In this example,
we can see it being used with database columns.
We select the firstname and lastname columns. When we run this query,
we can see the results, which show a combination of both names.
Just like earlier examples, there is no space in the output because there is no space in the data.
We can add a space by adding another parameter inside CONCAT. The CONCAT function accepts more
than two parameters, so we can add a space between the firstname and lastname columns like this.
When we run this query, this is what we see. We have a combined
firstname and lastname value separated by a space.
So that’s how you can concatenate values in PostgreSQL.
This table here summarises the different ways to concatenate data.
In Oracle, MySQL and PostgreSQL, you use the double pipe character,
and in SQL Server you use the plus character. All four databases have a CONCAT function,
but Oracle only supports two strings and other databases support many more.
And that brings us to the end of this video on how to concatenate values in SQL.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development, visit databasestar.com.
That’s where I share my best database-related content.
Which tip from this video was the most helpful? Was it using either double
pipes or plus to concatenate strings, or the fact that a space is not added
automatically but can be added if you need it, or something else?
Thanks for watching.