mysql query splicing

MySQL query splicing

When using MySQL to query, sometimes you need to splice multiple fields or fields in multiple tables. In this case, you can use the string splicing function provided by MySQL to operate. Commonly used string concatenation functions include CONCAT, CONCAT_WS and GROUP_CONCAT.

1. CONCAT function

CONCAT function is used to connect two or more strings or fields. The syntax is:

CONCAT(string1, string2, ...)

Among them, string1, string2 ... represents the string or field to be connected, which can be a constant, column name, expression, function, etc. For example, to find the name and position of an employee, you can use the following statement:

SELECT CONCAT(last_name, ' ', first_name) AS name, job_title
FROM employees

The CONCAT function here concatenates last_name and first_name and separate them with spaces as a field name of the query result. The running results are as follows:

name	job_title
Geoni Bakewell	Production Manager
Lemuel Marmie	Shipping Manager
Lino Uselman	Engineer
Honey Tunney	Sales Manager
...	...

2. CONCAT_WS function

The CONCAT_WS function is also used for string concatenation, but unlike the CONCAT function, it can specify a delimiter. The syntax is:

CONCAT_WS(separator, string1, string2, ...)

Among them, separator represents the separator, which can be any string; string1, string2... represent the strings or fields to be connected, and multiple strings are separated by commas. For example, to find the full name and email address of an employee, you can use the following statement:

SELECT CONCAT_WS(' ', first_name, last_name) AS full_name, email
FROM employees

The CONCAT_WS function here uses spaces as separators to concatenate first_name and last_name as a field full_name in the query result. The running results are as follows:

##Lino Uselmanluselman@classicmodelcars.comHoney Tunneyhtunney@classicmodelcars.com......

full_name	email
Geoni Bakewell	gbakewell@classicmodelcars.com
Lemuel Marmie	lmarmie@classicmodelcars.com

GROUP_CONCAT function

The GROUP_CONCAT function is used to combine multiple fields or records into To concatenate a certain field into a string, the syntax is:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

where expr represents the column or expression to be concatenated; ORDER BY and SEPARATOR represent sorting and separator respectively, which are optional. It should be noted that by default, the string returned by the GROUP_CONCAT function has a length limit, which can be changed by setting the parameter group_concat_max_len.

For example, to query the names of all employees in each department and concatenate them into a string separated by commas, you can use the following statement:

SELECT department, GROUP_CONCAT(last_name ORDER BY employee_number ASC SEPARATOR ', ') AS employee_names
FROM employees
GROUP BY department

The GROUP_CONCAT function here will last_name according to employee_number is sorted in order and separated by commas and spaces as a field employee_names in the query results. The running results are as follows:

departmentemployee_names##Admin##Customer ServiceTseng, Vanauf, Von ^^^^^^FinanceBow, Firrelli, Patterson, Tseng...Summary

Murphy, Patterson, Bondur, Ferrante, Firrelli
	##...

During the MySQL query process, we often need to splice multiple fields or fields in multiple tables. When implementing this function, MySQL provides three string splicing functions: CONCAT, CONCAT_WS and GROUP_CONCAT, which are used to connect two or more strings, connect any multiple strings and separate them with specified delimiters, and combine multiple fields. Concatenated into a string. Using these functions can greatly facilitate our data processing and improve query efficiency.

The above is the detailed content of mysql query splicing. For more information, please follow other related articles on the PHP Chinese website!