w3resource

MySQL CONCAT() function

CONCAT() function

The CONCAT() function in MySQL is a versatile tool used for string concatenation. It allows you to combine two or more strings into a single string. This function is especially useful for data formatting, combining fields, and creating readable outputs from data.

Key Points:

  • Versatility in Arguments:
    • You can pass multiple arguments to the CONCAT() function. These arguments can be strings, numbers, or expressions.

    • If any argument is NULL, the result of the CONCAT() function will also be NULL.
  • Binary and Nonbinary Strings:
    • The function returns a nonbinary string if all arguments are nonbinary.

    • It returns a binary string if any of the arguments are binary.
  • Implicit Conversion:
    • Numeric arguments are implicitly converted to their equivalent nonbinary string form before concatenation.

    This function is useful in -

    • String concatenation: It allows us to join multiple strings together into a single string.

    • Data formatting: CONCAT() can be used to format and present data in a desired format.

    • Expression evaluation: The CONCAT() function can also be used to concatenate the result of expressions or functions.

    Practical Applications:

  • Combining Columns:
    • Use CONCAT() to combine multiple columns into a single output, which can be useful for creating full names, addresses, or other combined fields.
  • Conditional String Formatting:
    • You can use CONCAT() with other functions to create conditional outputs or formatted strings.
  • Query Customization:
    • Use the CONCAT() function within queries to generate customized outputs directly from the database.

    Syntax:

    CONCAT (string1, string2,…)
    

    Arguments:

    Name Description
    string1 First string to be joined.
    string2 Second string to be joined. Up to N number of strings can be specified this way.

    Syntax Diagram:

    MySQL CONCAT() Function - Syntax Diagram

    MySQL Version: 8.0

    MySQL: CONCAT() Function - w3resource

    Pictorial representation of MySQL CONCAT() function

    pictorial representation of MySQL CONCAT function

     

    Examples: MySQL Concat() Function

    Table of Contents :

    Example of MySQL Concat Function using agruments

    One argument:

    mysql> SELECT CONCAT('w3resource');
    +----------------------+
    | CONCAT('w3resource') |
    +----------------------+
    | w3resource           |
    +----------------------+
    1 row in set (0.00 sec)
    

    Two or more arguments:

    mysql> SELECT CONCAT('w3resource','.','com');
    +--------------------------------+
    | CONCAT('w3resource','.','com') |
    +--------------------------------+
    | w3resource.com                 |
    +--------------------------------+
    1 row in set (0.00 sec)
    

    One of the arguments is NULL :

    mysql> SELECT CONCAT('w3resource','.','com',NULL);
    +-------------------------------------+
    | CONCAT('w3resource','.','com',NULL) |
    +-------------------------------------+
    | NULL                                |
    +-------------------------------------+
    1 row in set (0.02 sec)
    

    Numeric argument:

    mysql> SELECT CONCAT(102.33);
    +----------------+
    | CONCAT(102.33) |
    +----------------+
    | 102.33         |
    +----------------+
    1 row in set (0.00 sec)
    

    For quoted strings, concatenation can be performed by placing the strings next to each other :

    mysql> SELECT 'w3resource' '.' 'com';
    +----------------+
    | w3resource     |
    +----------------+
    | w3resource.com | 
    +----------------+
    1 row in set (0.00 sec)
    

    Example of MySQL CONCAT() function on columns

    The following MySQL statement will add values of pub_city column with values of the country column of publisher table placing a '-->' between them.

    Sample table: publisher


    Code:

    
    -- Select and format city and country from the 'publisher' table
    SELECT 
      -- Combine 'pub_city', a fixed string '--> ', and 'country' into one string
      CONCAT(pub_city, '--> ', country)
    -- Specify the table to retrieve data from
    FROM publisher;
    
    

    Explanation:

    • Function: This query retrieves and combines the pub_city and country columns from the publisher table.

    • Formatting: The CONCAT function merges the city (pub_city), a separator '--> ', and the country (country) into a single string.

    • Output Example: If pub_city is 'Paris' and country is 'France', the result will be 'Paris--> France'.

    Output:

    mysql> SELECT CONCAT(pub_city,'--> ',country)
        -> FROM publisher;
    +---------------------------------+
    | CONCAT(pub_city,'--> ',country) |
    +---------------------------------+
    | New York--> USA                 | 
    | Mumbai--> India                 | 
    | Adelaide--> Australia           | 
    | London--> UK                    | 
    | Houstan--> USA                  | 
    | New York--> USA                 | 
    | Cambridge--> UK                 | 
    | New Delhi--> India              | 
    +---------------------------------+
    8 rows in set (0.00 sec)
    

    MySQL CONCAT using WHERE clause

    The following MySQL statement will add pub_city and country column by a '-->' for those publishers whose concatinated name and country office is 'Ultra Press Inc. London'

    Code:

    Sample table: publisher


    Output:

    
    -- Select and format city and country from the 'publisher' table
    SELECT 
      -- Combine 'pub_city' and 'country' into one string with an arrow in between
      CONCAT(pub_city, '--> ', country)
    -- Specify the 'publisher' table as the source of data
    FROM publisher
    -- Filter results to include only rows where 'pub_name' and 'country_office' match "Ultra Press Inc. London"
    WHERE CONCAT(pub_name, ' ', country_office) = "Ultra Press Inc. London";
    
    

    Explanation:

    • Function: This query retrieves and combines the pub_city and country columns from the publisher table into a formatted string.

    • Filtering: It filters the results to show only those rows where the concatenated pub_name and country_office match "Ultra Press Inc. London".

    • Output Example: If the pub_city is 'London' and country is 'UK' for the matching publisher, the result will be 'London--> UK'.
    mysql> SELECT CONCAT(pub_city,'--> ',country)
        -> FROM publisher
        -> WHERE CONCAT(pub_name,' ',country_office)="Ultra Press Inc. London";
    +---------------------------------+
    | CONCAT(pub_city,'--> ',country) |
    +---------------------------------+
    | London--> UK                    |
    +---------------------------------+
    1 row in set (0.02 sec)
    

    MySQL CONCAT returns NULL if any field contain NULL

    The following MySQL statement will add book name and pub_lang column by a '-->' for all the books.

    Sample table: book_mast


    Code:

    
    -- Select and format book name and publication language from the 'book_mast' table
    SELECT 
      -- Combine 'book_name' and 'pub_lang' into one string with an arrow in between
      CONCAT(book_name, '--> ', pub_lang)
    -- Specify the 'book_mast' table as the source of data
    FROM book_mast;
    
    

    Explanation:

    • Function: This query retrieves data from the book_mast table.

    • Formatting: It combines the book_name and pub_lang into a single string, separated by '--> '.

    • Output: For example, if book_name is '1984' and pub_lang is 'English', the result will be '1984--> English'.

    • Use Case: This is useful for displaying book names along with their publication languages in a clear, combined format.

    Output:

    mysql> SELECT CONCAT(book_name,'--> ',pub_lang)
        -> FROM book_mast;
    +------------------------------------------------+
    | CONCAT(book_name,'--> ',pub_lang)              |
    +------------------------------------------------+
    | Introduction to Electrodynamics--> English     |
    | Understanding of Steel Construction--> English |
    | Guide to Networking--> Hindi                   |
    | Transfer  of Heat and Mass--> English          |
    | NULL                                           |
    | Fundamentals of Heat--> German                 |
    | Advanced 3d Graphics--> Hindi                  |
    | Human Anatomy--> German                        |
    | Mental Health Nursing--> English               |
    | Fundamentals of Thermodynamics--> English      |
    | The Experimental Analysis of Cat--> French     |
    | The Nature  of World--> English                |
    | Environment a Sustainable Future--> German     |
    | NULL                                           |
    | Anatomy & Physiology--> Hindi                  |
    | Networks and Telecommunications--> French      |
    +------------------------------------------------+
    16 rows in set (0.01 sec)
    

    The above output shows that, when the value of any of the two columns mention above is NULL, the output returns NULL, mention by red color.

    MySQL CONCAT using JOINS and wildcard character

    The following MySQL statement will show the combination of first name and last name and job title for those employees who contains the word Smith to their first and last name combination.

    Sample table: employees


    Sample table: jobs


    Output:

    Code:

    
    -- Selects and formats employee names and their job titles from the 'employees' and 'jobs' tables
    SELECT 
      -- Combines 'first_name' and 'last_name' into one string, aliased as 'name'
      CONCAT(first_name, ' ', last_name) AS "name", job_title
    -- Specifies 'employees' (aliased as 'e') and 'jobs' (aliased as 'j') as data sources
    FROM employees e, jobs j
    -- Filters results to include only rows where the employee's job ID matches the job ID in the 'jobs' table
    WHERE e.job_id = j.job_id
    -- Further filters to include only rows where the concatenated 'first_name' and 'last_name' contain 'Smith'
    AND CONCAT(first_name, ' ', last_name) LIKE '%Smith%';
    
    

    Explanation:

    • Purpose: Retrieves and formats employee names and their job titles, specifically for employees whose names contain 'Smith'.

    • Data Retrieval: Combines data from employees and jobs tables using an inner join.

    • Filtering: Matches employee and job IDs between tables and filters further to include only employees with 'Smith' in their name.

    • Output: Displays formatted employee names and their job titles.

    • Use Case: Useful for quickly identifying and displaying job information for employees with a specific last name ('Smith').
    mysql> SELECT CONCAT( first_name,  ' ', last_name ) AS  "name", job_title
        -> FROM employees e, jobs j
        -> WHERE e.job_id = j.job_id
        -> AND CONCAT( first_name,  ' ', last_name ) LIKE  '%Smith%';
    +---------------+----------------------+
    | name          | job_title            |
    +---------------+----------------------+
    | Lindsey Smith | Sales Representative |
    | William Smith | Sales Representative |
    +---------------+----------------------+
    2 rows in set (0.00 sec)
    

    Try the following Queries

    Sample table : publisher


    Write a SQL statement to display the publisher city and name according to the group on publisher city.

    Write a SQL statement to display the publisher city and name and country office with a suitable title for those publishers which country office and publishing city are in the same place.

    Write a SQL statement to display the publisher name, country office and a maximum number of branches with the suitable title for those publishers who maintain on and above 15 branches worldwide.

     Want to practice more MySQL exercises? Click here to get three hundred plus exercises with solutions.

    Frequently Asked Questions (FAQ) - MySQL CONCAT Function

    1. What is the MySQL CONCAT() function used for?

    The CONCAT() function in MySQL is a versatile tool used for string concatenation. It allows you to combine two or more strings into a single string.

    2. How many arguments can be passed to the CONCAT() function?

    We can pass multiple arguments to the CONCAT() function. These arguments can be strings, numbers, or expressions.

    3. What happens if any argument passed to CONCAT() is NULL?

    If any argument passed to CONCAT() is NULL, the result of the CONCAT() function will also be NULL.

    4. What type of string does the CONCAT() function return?

    The CONCAT() function returns a nonbinary string if all arguments are nonbinary. It returns a binary string if any of the arguments are binary.

    5. How are numeric arguments handled by the CONCAT() function?

    Numeric arguments are implicitly converted to their equivalent nonbinary string form before concatenation.

    6. What are the practical applications of the CONCAT() function?

    String concatenation, data formatting, and expression evaluation are the main practical applications of the CONCAT() function.

    7. Can CONCAT() be used for combining columns?

    Yes, CONCAT() is commonly used to combine multiple columns into a single output, which is useful for creating full names, addresses, or other combined fields.

    8. How can CONCAT() be used for conditional string formatting?

    CONCAT() can be combined with other functions to create conditional outputs or formatted strings based on specific criteria.

    9. Can CONCAT() be used within queries to customize outputs?

    Yes, CONCAT() function can be used within queries to generate customized outputs directly from the database.

    Video Presentation:

    All String Functions (Slides presentation)

    Previous: CONCAT_WS
    Next: ELT

    

    Follow us on Facebook and Twitter for latest update.