MySQL provides many useful functions to work with string literals and text based columns. Sometimes you may need to concatenate strings in MySQL. They may be of same of different data types. In this article, we will learn how to concatenate strings in MySQL, with different use cases. We will use concat() function to concatenate strings in MySQL, since it allows you to concatenate literals with other literals or with columns, and it also allows concatenation between only columns, even if they have different data types.
How to Concatenate Strings in MySQL
Here are the steps to concatenate strings in MySQL.
1. Concatenate strings using Concat()
Concat() is the most common function to concatenate strings in MySQL. Here is the syntax for concat()
concat(string1, string2, ...)
In the above function, you need to specify the strings to be concatenated, in a comma-separated manner – first string as first argument, second string as second argument, and so on. Each string can be a literal within single/double quotes or a column name.
Here is an example to concatenate first_name and last_name column with a space in between to obtain full name.
SELECT CONCAT(first_name, " ", last_name) AS Full_name FROM students
Here is the sample output.
Full_name ---------- John Doe Jane Doe ...
If you don’t provide an alias to the concat() function then column name of result will be the entire concat() function as defined in the SQL query. Here is an example where we don’t alias the concat() function
SELECT CONCAT(first_name, " ", last_name) FROM students
Here is the sample output.
CONCAT(first_name, " ", last_name) ---------------------------------- John Doe Jane Doe ...
2. Concatenate with Comma
If you want to concatenate columns with comma then you can add it within quotes, as an argument in concat() function, as shown below. In the following query, we add age details after the full name, along with a comma in between.
SELECT CONCAT(first_name, " ", last_name,", ", Age) AS student_details FROM students
Here is the sample output.
student_details --------------- John Doe, 31 Jane Doe, 34 ...
3. Concatenate with type conversion
Generally, concat() function will automatically convert non-string data types like integers & dates into strings before concatenation, as seen in the above example. However, if you get an error saying type mismatch, then you can always use cast(), convert() or format() function to convert the data into string before combining with other strings.
SELECT CONCAT(first_name, " ", last_name,", ", cast(Age as char)) AS student_details FROM students
Here is the sample output.
student_details --------------- John Doe, 31 Jane Doe, 34 ...
However, please note, if any of the strings in concat() function is NULL, then the result of concatenation is NULL.
That’s it. In this short article, we have learnt how to concatenate multiple strings using concat() function.
Also read:
What is Basename Command in Shell Script
How to Compare Strings in Python
How to Copy List in Python
How to Copy Files in Python
How to Comment in Python
Related posts:
Sreeram has more than 10 years of experience in web development, Python, Linux, SQL and database programming.