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

This article will explain in detail how to achieve data type conversion in SQL Server. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

1 explicit conversion

A display conversion is an explicit conversion of an expression of one data type to another. Commonly used are the CAST and CONVERT functions.

CAST: CAST (expression AS data_type) CONVERT: CONVERT (data_type [(length)], expression [, style])

The parameter expression is any valid Microsoft SQL Server expression. The data types provided by the data_type target system cannot use user-defined data types.

2 recessive conversion

The implicit conversion is invisible to the user and is handled automatically by the SQL Server engine. Implicit conversion automatically converts data from one data type to another. For example, if a smallint variable is compared to an int variable, the smallint variable is implicitly converted to an int variable before the comparison. Some implicit and explicit data type conversions are not supported when converting from one SQL Server object's data type to another. For example, nchar values cannot be converted to image values at all. Nchar can only be explicitly converted to binary, and implicit conversion to binary is not supported. Nchar can be converted to nvarchar either explicitly or implicitly.

3 the risk of recessive conversion

Implicit conversion is sometimes very convenient to simplify SQL scripts, but there is also a potential risk that it may be normal at the beginning of the script, but an inexplicable error occurs after a certain point in time. Here is an example from a real project. In SQL Server 2008, there is a table that needs to pull data from two different data tables. Because the two data tables belong to different systems, their primary key types are different, one is int type, the other is GUID. At first, I thought that both of them could be converted to character types for storage. So create a mixed ID column of nvarchar (50) in the table as the primary key. As shown in the following figure:

At first, the data pulled does not have the value of GUID, it is all data converted from the INT type, so the SQL script runs normally, but suddenly when running, there is a "failure to convert the nvarchar value '4C185367-F004-41FEmur8A0A color DB4E819B1FF2' to the data type int." Donovan's mistake. As shown in the following figure:

Navigate to the script and execute the following SQL:

Select * from dbo.Demo where mixed ID=305

The data in the primary key includes characters converted by GUID and strings converted by INT. The example data is as follows:

However, if the following SQL is executed, it will all be executed normally:

Select * from dbo.Demo where mixed ID=305 and name = 'INT'select * from dbo.Demo where mixed ID=305 and serial number =' 2'select * from dbo.Demo where mixed ID=305 and serial number = 2select * from dbo.Demo where mixed ID='305' and name = 'INT'select * from dbo.Demo where mixed ID='305'

When select * from dbo.Demo where mixed ID=305 is executed, SQL Server converts the implicit of nvarchar type to int type. If there are no characters of GUID type in the data, the conversion is normal. If so, the conversion fails when the implicit conversion of GUID characters to INT is performed.

On how to achieve data type conversion in SQL Server to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.