Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "sample analysis of string splicing in SQL", which is easy to understand and clear. I hope it can help you solve your doubts. Let the editor lead you to study and study the "sample analysis of string splicing in SQL".

I. Overview

I believe that in daily development, string concatenation is often needed in SQL statements. Take the three sqlserver,oracle,mysql databases as an example, because these three databases are representative.

Sqlserver:

Select '123 please call 456.

Oracle:

Select '123' | |' 456 'from dual

Or

Select concat ('123') from dual

Mysql:

Select concat ('123', '456')

Note: there is no concat function in SQL Server (the concat function has been added to SQL Server 2012). Although there is concat in both oracle and mysql, only two strings can be concatenated in oracle, so it is recommended to use | |, while concat in mysql can concatenate multiple strings.

The "+" sign in SQL Server can not only perform string concatenation, but also perform numeric operations, which should be used carefully when doing string concatenation. The following is a detailed analysis of the "Users" table:

Number + string

2.1 int + varchar

SELECT id + place FROM Users WHERE id = 1; / error "failed to convert varchar value 'bzz' to data type int" SELECT id + place FROM Users WHERE id = 5; / / error "failed to convert varchar value' 102.34'to data type int" SELECT id + place FROM Users WHERE id = 4; / / returned 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; / / prompt error "error converting from data type varchar to numeric."

Thus, the system converts the string varchar type to int, prompts an error if it cannot be converted, and calculates the number if the conversion is successful.

III. Numbers + numbers

Numbers refer to int, decimal, and so on. Number + number, the number is added. If a field is NULL, the result is NULL.

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

4. String + string

String + string, it is directly concatenated. If a field is NULL, the result is NULL.

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

Type conversion using CAST and CONVERT functions

From the above example, we can see that the safest way to use "+" for string concatenation or numeric calculation is to do type conversion.

The CAST () function can convert an expression of one data type to another

The CONVERT () function can also convert the specified data type to another data type.

Requirement: convert "678" into numerical data and add it to 123 for mathematical operation.

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

Requirement: id column and place column for string concatenation.

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

The concatenated string cannot simply be used as a "filter field".

Sometimes, you need to filter column A = variable 1 and column B = variable 2 in order to simplify the SQL statement column A + column B = variable 1 + variable 2. This method is not entirely accurate.

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

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

To prevent this from happening, you can add a more special string between column An and column B.

SELECT * FROM Users WHERE uname + 'rain@&%$man' + place =' aa' + 'rain@&%$man' +' bzz' are all the contents of this article entitled "sample Analysis of string concatenation in SQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.