Therefore, the query may not return any result. If you supply this result to the IN operator, the query is not working.
It means you cannot use the result of the GROUP_CONCAT() function for IN operator e.g., within a subquery.įor example, the GROUP_CONCAT() function returns the result of values: 1 2, and 3 as the ‘1,2,3’ string. The GROUP_CONCAT() function returns a single string, not a list of values. MySQL GROUP_CONCAT function: common mistakes Note that GROUP_CONCAT() function concatenates string values in different rows while the CONCAT_WS() or CONCAT()function concatenates two or more string values in different columns. SELECT GROUP_CONCAT(ĬONCAT_WS( ', ', contactLastName, contactFirstName) The following query makes a list of semicolon-separated values of customers. Then, you use the GROUP_CONCAT() function to make the list.First, you concatenate the last name and first name of each customer’s contact using the CONCAT_WS() function.Sometimes, the GROUP_CONCAT function can be combined with the CONCAT_WS function to make a result of query more useful.įor example, to make a list of semicolon-separated values of customers: Using MySQL GROUP_CONCAT() with CONCAT_WS() function example
#MYSQL CONCAT CODE#
ORDER BY firstName, lastname Code language: SQL (Structured Query Language) ( sql ) Now, we can group the result set by the employee number and concatenate all employees that are being in charge of the employee by using the GROUP_CONCAT() function as follows: SELECTĬustomers ON customers.salesRepEmployeeNumber = employeeNumber Lastname Code language: SQL (Structured Query Language) ( sql ) To find out who in charge of which customers, you use the inner join clause as follows: SELECTĬustomers ON customers.salesRepEmployeeNumber = employees.employeeNumber In other words, each sales employee is in charge of one or more customers. Let’s put it in a practical example.Įach customer has one or more sale representatives. Great! now you know how the GROUP_CONCAT() function works. To change the default separator of the returned string from a comma (,) to a semi-colon ( ), you use the SEPARATOR clause as the following query: SELECT GROUP_CONCAT( DISTINCT country To sort the country’s name before concatenating, you use the ORDER BY clause as follows: SELECT GROUP_CONCAT( DISTINCT country It is more readable if the country’s names are in ascending order. To remove the duplicate country’s names, you add the DISTINCT clause as the following query: SELECT GROUP_CONCAT( DISTINCT country) However, some customers located in the same country. To get all countries where customers locate as a comma-separated string, you use the GROUP_CONCAT() function as follows: SELECT GROUP_CONCAT(country)Ĭustomers Code language: SQL (Structured Query Language) ( sql )
Let’s take a look at the customers table in the sample database. In case you need more than this, you can extend the maximum length by setting the group_concat_max_len system variable at SESSION or GLOBAL level. by default, the maximum length of the return string is 1024. The GROUP_CONCAT function returns a binary or non-binary string, which depends on the arguments. It returns NULL if there was no matching row found or all arguments are NULL values. The GROUP_CONCAT function ignores NULL values. If you do not specify a separator, the GROUP_CONCAT function uses a comma (,) as the default separator.
The SEPARATOR specifies a literal value inserted between values in the group. If you want to sort the values in the descending order, you need to specify explicitly the DESC option. By default, it sorts the values in ascending order. The ORDER BY clause allows you to sort the values in ascending or descending order before concatenating. The DISTINCT clause allows you to eliminate duplicate values in the group before concatenating them.
T Code language: SQL (Structured Query Language) ( sql ) The following example demonstrates how the GROUP_CONCAT() function works. ) Code language: SQL (Structured Query Language) ( sql ) The following shows the syntax of the GROUP_CONCAT() function: GROUP_CONCAT( The MySQL GROUP_CONCAT() function is an aggregate function that concatenates strings from a group into a single string with various options. Introduction to MySQL GROUP_CONCAT() function
#MYSQL CONCAT HOW TO#
Summary: in this tutorial, you will learn how to use the MySQL GROUP_CONCAT() function to concatenate strings from a group with various options.
0 kommentar(er)
