MySQL Concat

Introduction of MySQL Concat

MySQL Concat function is used when dealing with string in a database that can be in any format, column values, variables or literal values in the string that helps to append two or more such string values to each other to create a new string value that is returned by the function that is the resultant value consisting all the string values passed to it in appended format and forming one large string. In this article, we will learn about how we can use MySQL Concat function to concatenate two or more string values, study its syntax and working and implement few examples that will demonstrate the usage of Concat function in MySQL.

Syntax and Working of Concat Function:

The syntax of Concat function in MySQL is as defined below:

CONCAT(string_expression1,string_expression2, ... );

We can provide as many parameters as we want to concatenate using the Concat function. The string expressions can be any of the constant or literal string values, variables having string values, columns that store the string values with datatype varchar, or any string value specified in single or double-quotes. Note that in case if the Concat function is used without specifying any parameter value then it will result in an error. The minimum one parameter is a must for using the Concat function. Passing a NULL value in any of the parameters to the Concat function gives out the resultant value as NULL itself irrespective of what other values are passed as parameter.

Examples of MySQL Concat

Let us first try to execute the MySQL Concat function without passing any parameters and see the output which is arisen. We will execute the following query statement –

SELECT CONCAT();

The execution of the above query statement gives the following output –

We can observe from the output that an error is raised saying that an incorrect number of parameters were supplied to the Concat function.

Now, we will concatenate the string values “EDUCBA”, “IS A GREAT “, “PLATFORM “, “TO EXPAND “, “YOUR HORIZONS ” and “OF LEARNING” using the Concat function in MySQL. For this, we will use the following query statement –

SELECT CONCAT("EDUCBA", "IS A GREAT ", "PLATFORM ", "TO EXPAND ", "YOUR HORIZONS ", "OF LEARNING") AS "Final String";

The execution of the above query statement gives the following output:

Let us try concatenating the strings that have NULL value as one of its parameters in the Concat function. We will try concatenating “Welcome”, “To “, “EDUCBA ” and NULL parameters using the following query –

SELECT CONCAT("Welcome", "To ", "EDUCBA ", NULL);

The execution of the above query statement gives the following output as NULL because even a single NULL parameter can result in output string being NULL:

We can even concatenate the values stored inside the variables and column values. Let us create on the table named dictionary that will contain three columns in it namely, word, description, and meaning. We will use the following table to create the table:

CREATE TABLE `dictionary` (
`word` varchar(100) DEFAULT NULL,
`meaning` varchar(5000) DEFAULT NULL,
`description` varchar(5000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The execution of the above query statement gives the following output:

Let us insert some records in the table using the following query statement:

INSERT INTO `dictionary` (`word`, `meaning`, `description`) VALUES
('antonym', 'a word that means the opposite of another word', 'an Alternative name'),
('connotation', 'an additional idea or emotion that a word suggests to you', 'emotions attached to words' ),
('etymology', 'the study of the origins of words; the origins of a particular word', 'the study of the origins'),
('lexicography', 'the job or skill of writing dictionaries', 'writing dictionaries'),
('polysemy','the fact that some words can have more than one meaning', 'multiple meaning words'),
('thesaurus', 'a reference tool which shows groups of words that have similar meanings', 'representation of groups of words that have similar meanings'),
('knack', 'an acquired or natural skill at doing something.', 'natural skill'),
('flair', 'stylishness and originality.', 'originality'),
('panache', 'a tuft or plume of feathers', 'feather collection');

The execution of the above query statement gives the following output:

Let us retrieve the inserted records:

SELECT * FROM dictionary;

gives following output:

Now, we have to concatenate the column values such that the resultant string will have the strings in format Word “word column string” means that “meaning string”. We can use the Concat function to do so in the following query statement:

SELECT CONCAT('Word ', word, ' means that ', meaning) AS 'Meaning of Words' FROM dictionary;

The execution of the above query statement gives the following output with expected strings:

An alternative way of concatenating strings in MySQL:

MySQL provides a very easy facility of concatenating the string which is specifying the string wrapped in single or double quotes and separated by space that automatically results in the output being a single collective string that appends all the strings that we had specified separated by the space. Consider a simple example where we want to concatenate the string values “GOOD”, ” MORNING!”, ” HAVE”, “A NICE” and ” DAY!”. For this, we can use the simple SELECT statement to retrieve the concatenated string. We will use the following query statement to concatenate the strings in MySQL:

SELECT "GOOD"" MORNING!"" HAVE""A NICE"" DAY!";

The execution of the above query statement gives the following output after execution:

concat_ws is the function that is provided in MySQL that can be used as an alternative to concat and normal select statement to concat the string values. concat_ws function provides us the facility to specify the separator and this function ignores the NULL values if passed as parameter. The syntax is the same as the concat function with the first parameter being separator. Consider one example –

SELECT CONCAT_WS(",","Payal", "Heena ", "Sonam ", NULL) AS NAMES;

where the comma is the separator that gives the following output:

Conclusion

We can concatenate the string in MySQL by using simple SELECT statements or CONCAT and CONCAT_WS functions that are available in MySQL. We need to specify at least one parameter to the Concat function and be careful that none of the parameters should have a NULL value in it.

Recommended Articles

This is a guide to MySQL Concat. Here we also discuss the introduction and syntax and working of Concat function along with different examples and its code implementation. You may also have a look at the following articles to learn more –

1. MySQL DDL
2. MySQL CASE Statement
3. MySQL Rename Column
4. Grant Privileges MySQL