AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (55 Blogs)
  • SQL Essentials Training and Certification (9 Blogs)
SEE MORE

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-widget-banner-bg

Learn About Concatenate In SQL With Examples

Published on Oct 31,2019 19 Views

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

Concatenation, in general, refers to binding a bunch of strings into a single string. In SQL, this is achieved by a function named CONCAT(). It takes up to 255 input strings and joins them together. In this article, we will learn how we can use the CONCAT() function in SQL. The following topics are covered in this blog:

CONCAT Function In SQL

In SQL, the concatenation of strings is achieved by the CONCAT() function. There are a few things you should keep in mind while using the CONCAT function.

  • The CONCAT function raises an error if only one string is passed as input. There has to be at least two strings as input for the CONCAT function to work without any hassles.

  • If at all, non-character string values are passed as input. The CONCAT function will implicitly convert those values before the concatenation.

  • The CONCAT function can take up to 255 input strings for concatenation.

How To Use CONCAT In SQL

To understand how we can use CONCAT in SQL, let us take a simple example. So ideally concatenation works like- let’s say we have two strings, “edureka”, “SQL”. And if we concatenate these two strings, we will get a resultant string or concatenated string as “edureka SQL”. It works the same with the CONCAT function as well.

Let’s say we have the same strings “edureka” and “SQL”, to concatenate these two strings we will write the following command.

SELECT CONCAT("edureka", "SQL");

Output: edurekaSQL

We can use the addition “+” operator to add two or more strings together.

SELECT "edureka" + "SQL";

Output:edurekaSQL

To separate the strings with a separator, we can use CONCAT_WS() function as well. Take a look at an example below to understand how it works.

SELECT CONCAT_WS("-" , "EDUREKA", "SQL");

Output: EDUREKA-SQL

So you can use either of these approaches to concatenate strings in SQL. Let us take one more look at the parameters that we pass into the CONCAT function.

Concatenation Parameters

  • CONCAT Parameters – The only required parameters are the string values that need to be concatenated separated by a comma.

  • Addition Operator Parameters – It does not require anything more than the strings separated by a comma that needs to be concatenated.

  • CONCAT_WS Parameters – The first parameter is the separator that you want to use, after that all the strings that are being concatenated are added, all separated by a comma.

CONCAT Function Examples

Let us take a simple example using the string literals.

SELECT 'edureka' + 'SQL' as full_name;

Output: edurekaSQL

Let us take one more example

SELECT CONCAT('edureka', 'sql');

Output: edurekasql

Now let us try to understand how concatenation works with table values.

Using CONCAT With Table Values

Let us consider a table with the following values.

table - concatenate sql - edureka

Now let us try to concatenate the first name and the last name.

SELECT first_name,last_name, 
CONCAT(first_name,' ',last_name)full_name 
FROM N
ORDER BY full_name

Output: table 2 - concatenate sql - edureka

Considering the null values in a table, let us understand how concatenation works with null values.

Using CONCAT With Null Values

Let us suppose we have a few null values in the table. When the value is NULL the CONCAT function uses empty for concatenation.

SELECT first_name,last_name,phone, 
CONCAT(first_name,' ',last_name,phone)full_name 
FROM N
ORDER BY full_name

Output: table 3 - concatenate sql - edureka

So that was all about Concatenation in SQL, I hope this article has helped you in adding value to your knowledge. For more information on SQL or Databases, you can refer to our comprehensive reading list here: Databases Edureka.

If you wish to get a structured training on MySQL, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of ”Concatenate SQL” and I will get back to you.

Comments
0 Comments

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.