Explain in depth the String concatenation in SQL and explain the SQL String concatenation

Explain in depth the String concatenation in SQL and explain the SQL String concatenation

I. Overview

I believe that in daily development, String concatenation is often required in SQL statements. Take sqlserver, oracle, and mysql databases as examples, because these three databases are representative.

Sqlserver:

select '123'+'456';

Oracle:

select '123'||'456' from dual;

Or

select concat('123','456') from dual;

Mysql:

select concat('123','456');

Note:SQL Server does not have the concat function (SQL Server 2012 has added the concat function ). Although concat exists in oracle and mysql, only two strings can be concatenated in oracle. Therefore, we recommend that you use the | method. In mysql, concat can be spliced into multiple strings.

In addition to String concatenation, "+" in SQL Server can also be used for numeric operations. Be careful when splicing strings. The following uses the "Users" table as an example for detailed analysis:

2. Number + String

2.1 int + varchar

SELECT id + place FROM Users WHERE id = 1; // The error "failed to convert varchar value 'bzz 'to data type int" appears. SELECT id + place FROM Users WHERE id = 5; // The error message "set the varchar value to 102. 34' failed to convert to data type int"

SELECT id + place FROM Users WHERE id = 4; // return int "105"

2.2 decimal + varchar

SELECT *, id + cost FROM Users WHERE id = 4 OR id = 5; // return decimal "102.98" and "104.30"

SELECT *, place + cost FROM Users WHERE id = 1; // the error message "An error occurred while converting FROM data type varchar to numeric ."

As you can see, the system will convert the varchar type of the string to int. If the conversion fails, an error is prompted. If the conversion is successful, the number is calculated.

Iii. Numbers + numbers

Numbers are int, decimal, and other types. Number + number, add the number. If a field is NULL, the calculation result is NULL.

SELECT *, uage + cost AS 'uage + cost' FROM Users

Iv. String + String

String + String, then directly splice. If a field is NULL, the calculation result is NULL.

SELECT *, uname + place AS 'uname + place' FROM Users 

5. Use the CAST and CONVERT functions for type conversion

Through the above example, we can see that if you want to use "+" for String concatenation or Digital computation, the most secure method is type conversion.

• CAST()A function can convert a data type expression to another data type.
• CONVERT()The function can also convert the specified data type to another data type.
  

Requirements:Convert "678" to numeric data and add it to 123 for mathematical operations.

SELECT CAST('678' AS INT) + 123;SELECT CONVERT(INT, '678') + 123;

Requirements:The id column and the place column are concatenated by strings.

SELECT *, CONVERT(varchar(10), id) + place FROM Users;

The concatenated string cannot be simply used as a "Filter field"

Sometimes, column A = variable 1 and column B = variable 2 are required for filtering. to simplify the SQL statement column A + Column B = variable 1 + variable 2. This method is not completely accurate.

SELECT * FROM Users WHERE uname + place = 'aabzz';

SELECT * FROM Users WHERE uname = 'aa' AND place = 'bzz';

To prevent this, you can add A special string between column A and column B.

SELECT * FROM Users WHERE uname + 'rain@&%$man' + place = 'aa' + 'rain@&%$man' + 'bzz'

Summary

The above is all about String concatenation in SQL. I hope this article will help you learn and use SQL. If you have any questions, please leave a message, thank you for your support.