[Concatenation of strings in the turn]mysql

Concatenation of strings

1,mysql

In Java, C # and other programming languages, the concatenation of strings can be achieved by the plus "+", such as: "1" + "3", "a" + "B".
You can also use the plus sign "+" to connect two strings in MySQL, such as the following SQL:

SQL code

1. SELECT ' + ' + ', fage+' 1 ' from t_employee

Execution results

SQL code

1. ' + ' + 'fage+ '1 '
2. 45 26
3. 45 29
4. 45 24
5. 45 26
6. 45 29
7. 45 28
8. 45 24
9. 45 29
10. 45 23

MySQL attempts to convert the field value at both ends of the plus sign to a number type, and if the conversion fails, it considers the field value to be
0, for example, we execute the following SQL statement:

SQL code

1. SELECT ' abc ' +' 123 ', fage+' a ' from t_employee

After execution we can see the following results in the output:

HTML code

2. ' abc ' + ' 123 ' fage+ ' a '
3. 123 25
4. 123 28
5. 123 23
6. 123 25
7. 123 28
8. 123 27
9. 123 23
10. 123 28
11. 123 22

string concatenation in MySQL to use the concat function, the concat function supports one or more parameters,
Parameter types can be string types or non-string types, and for non-string type parameters MySQL will attempt to
Converting it to a string type, the Concat function will stitch all parameters into a string in the order of the parameters as
The return value. For example, the following SQL statement is used to query the user's multiple field information in the form of a computed field:

SQL code

1. SELECT CONCAT (' work number: ', Fnumber,' employee's Happiness Index: ', fsalary/(FAge-21))

From T_employee execution, we can see the following results in the output:

Java code

1. CONCAT (' work number: ', Fnumber,' employee's Happiness Index: ', fsalary/(fage-))
2. Work Number: DEV001 's employee's Happiness index:2075.000000
3. Work Number: DEV002 's employee's Happiness index:328.685714
4. Work Number: HR001 's employee's Happiness index:1100.440000

Another function Concat_ws for string stitching is provided in MySQL,
Concat_ws can add a specified delimiter between the strings to be stitched, and its first parameter value is the one used
The remaining parameters are string values to be stitched, such as executing the following SQL:

SQL code

1. SELECT Concat_ws (', ', fnumber,fage,fdepartment,fsalary) from t_employee

After execution we can see the following results in the output:

Java code

2. Concat_ws (', ', fnumber,fage,fdepartment,fsalary)
4. DEV001,25,development,8300.00
6. DEV002,28,development,2300.80
8. HR001,23,humanresource,2200.88
10. HR002,25,humanresource,5200.36
12. IT001,28,infotech,3900.00
14. IT002,27,infotech,2800.00
16. SALES001,23,sales,5000.00
18. SALES002,28,sales,6200.00
20. SALES003,22,sales,1200.00

2,oracle

Using "| |" in Oracle string concatenation, which is used in the same way as the plus sign "+" in MSSQLServer.
For example, execute the following SQL statement:

SQL code

1. SELECT ' work number is ' | | fnumber| | ' employee's name is ' | | FName from t_employee
2. WHERE FName is not NULL

After execution we can see the following results in the output:

HTML code

1. Work number is | | fnumber| | The Employee name is | | FNAME
2. Tom is the employee whose work number is DEV001
3. Employee name is DEV002, Jerry
4. Employee named John, SALES001
5. Employee named Kerry, SALES002
6. Employee named stone for work number SALES003
7. Employee named HR001, Jane
8. Employee named HR002, Tina
9. Employee named Smith for work number IT001

In addition to the "| |", Oracle supports string concatenation using the concat () function, such as executing the following SQL statement:

SQL code

1. SELECT CONCAT (' work number: ', fnumber) from t_employee

After execution we can see the following results in the output:

HTML code

1. CONCAT (Work No.:, Fnumber)
2. Work No.: DEV001
3. Work No.: DEV002
4. Work No.: HR001
5. Work No.: HR002
6. Work No.: IT001
7. Work No.: IT002
8. Work No.: SALES001
9. Work No.: SALES002
10. Work No.: SALES003

If the value connected in Concat is not a string, Oracle attempts to convert it to a string.

Unlike MySQL's concat () function, Oracle's CONCAT () function supports only two parameters and does not support two
Stitching on the string. If you are stitching multiple strings, you can use multiple concat () functions for nesting.

SQL code

1. SELECT CONCAT (CONCAT (CONCAT (' work number ', Fnumber),' employee name '), FName) from
2. T_employee
3. WHERE FName is not NULL

After execution we can see the following results in the output:

HTML code

1. CONCAT (CONCAT (CONCAT (work number, fnumber), employee name), FNAME)
2. Tom is the employee whose work number is DEV001
3. Employee name is DEV002, Jerry
4. Employee named John, SALES001
5. Employee named Kerry, SALES002
6. Employee named stone for work number SALES003
7. Employee named HR001, Jane
8. Employee named HR002, Tina
9. Employee named Smith for work number IT001

[Concatenation of strings in the turn]mysql