Type conversion in SQL-Learning

Cast and convert

Explicitly converts a data type expression to another data type. Cast and convert provide similar functions.

Syntax

Use cast:

CAST ( expression AS data_type )

Use convert:

CONVERT (data_type[(length)], expression [, style])

Parameters

Expression

Is any valid Microsoft SQL Server expression. For more information, see expressions.

Data_type

Data Types provided by the target system, includingBigintAndSQL _variant. User-Defined data types cannot be used. For more information about available data types, see data types.

Length

Nchar,Nvarchar,Char,Varchar,BinaryOrVarbinaryOptional parameter of the data type.

Style

Date FormatDatetimeOrSmalldatetimeConvert data to character data (Nchar,Nvarchar,Char,Varchar,NcharOrNvarcharData Type); or string format styleFloat,Real,MoneyOrSmallmoneyConvert data to character data (Nchar,Nvarchar,Char,Varchar,NcharOrNvarcharData Type ).

SQL Server supports the data format in the Arabic style using the Kuwait algorithm.

In the table, the two columns on the left representDatetimeOrSmalldatetimeConvert to the style value of the character data. Add 100 to the style value to obtain the four-digit year (yyyy) of the century ).

Without Century digital (yy)	Digital Century (YYYY)	Standard	Input/Output **
-	0 or 100 (*)	Default Value	Mon dd yyyy hh: miam (or pm)
1	101	USA	Mm/DD/YYYY
2	102	ANSI	YY. Mm. dd
3	103	UK/France	Dd/mm/yy
4	104	Germany	Dd. mm. yy
5	105	Italy	DD-mm-yy
6	106	-	Dd mon YY
7	107	-	Mon DD, YY
8	108	-	Hh: mm: SS
-	9 or 109 (*)	Default Value + millisecond	Mon dd yyyy hh: MI: SS: mmmam (or pm)
10	110	USA	Mm-dd-yy
11	111	Japan	YY/MM/dd
12	112	ISO	Yymmdd
-	13 or 113 (*)	European default value + millisecond	Dd mon yyyy hh: mm: SS: Mmm (24 h)
14	114	-	Hh: MI: SS: Mmm (24 h)
-	20 or 120 (*)	ODBC specifications	Yyyy-mm-dd hh: mm: ss [. fff]
-	21 or 121 (*)	ODBC specifications (in milliseconds)	Yyyy-mm-dd hh: mm: ss [. fff]
-	126 (***)	Iso8601	Yyyy-mm-dd thh: mm: SS: Mmm (excluding spaces)
-	130 *	Kuwait	Dd mon yyyy hh: MI: SS: mmmam
-	131 *	Kuwait	Dd/mm/yy hh: MI: SS: mmmam

* The default value (Style 0, 100, 9, 109, 13, 113, 20, 120, 21, or 121) always returns century digits (yyyy ).
** When convertingDatetimeInput; output when converted to character data.
* ** It is specially used for XML. ForDatetimeOrSmalldatetimeToCharacterData conversion. The output format is shown in the table. ForFloat,MoneyOrSmallmoneyToCharacterData conversion. The output is equivalent to style 2. ForRealToCharacterData conversion. The output is equivalent to style 1.

 

ImportantBy default, SQL Server interprets two-digit years based on the end year 2049. That is, the year 49 with two digits is interpreted as 2049, and the year 50 with two digits is interpreted as 1950. Many client applications (such as client applications based on OLE automation objects) Use 2030 as the end year. SQL Server provides a configuration option ("") to change the end year used by SQL Server and process the date in a consistent manner. However, the safest way is to specify a four-digit year.

 

WhenSmalldatetimeWhen converting to character data, the style that contains seconds or milliseconds will display zero at these locations. WhenDatetimeOrSmalldatetimeYou can use the appropriateCharOrVarcharThe length of the Data Type to truncate the date part that is not required.

The following table showsFloatOrRealThe style value when converting to character data.

Value	Output
0(Default)	The maximum value is 6 digits. Use scientific notation as needed.
1	Always 8 bits. Always use scientific notation.
2	Always a 16-bit value. Always use scientific notation.

 

In the following table, the left column indicatesMoneyOrSmallmoneyThe style value when converting to character data.

Value	Output
0(Default)	Each three digits on the left of the decimal point are not separated by commas. The two digits on the right of the decimal point, for example, 4235.98.
1	Each three digits on the left of the decimal point are separated by commas. The two digits on the right of the decimal point, for example, 3,510.92.
2	Each three digits on the left of the decimal point are not separated by commas. The four digits on the right of the decimal point, for example, 4235.9819.

 

Return type

Returns the same value as Data Type 0.

Note

Implicit conversions refer to conversions that do not specify cast or convert functions. Explicit conversions refer to conversions of cast (convert) functions that have been specified. The following charts show all explicit and implicit conversions of data types available for the SQL Server System, includingBigintAndSQL _variant.

 

 

 

 

DescriptionBecause Unicode data always uses an even number of bytesBinaryOrVarbinaryA prompt is displayed when converting data types supported by Unicode. For example, this conversion does not return a hexadecimal value of 41, but returns the hexadecimal value of 4100: Select cast (0x41 as nvarchar) as varbinary)

 

Not SupportedTextAndImageAutomatic conversion of data types. You can setTextExplicitly convert data to character dataImageExplicit conversion of dataBinaryOrVarbinaryData, but the maximum length is 8000. If you try to perform an incorrect conversion (for example, convert a character expression containing lettersInt), SQL Server generates an error message.

When cast or convert outputs a string and the input is a string, the output has the same sorting rule and sorting rule label as the input. If the input is not a string, the default database sorting rule and the default mandatory sorting rule label are used. For more information, see sorting rule priority.

To assign different sorting rules to the output, apply the Collate clause to the Result Expression of the cast or convert function. For example:

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS

There is no marker for the assignment.SQL _variantImplicit conversion of data types, but there is conversionSQL _variantImplicit conversion.

Convert the string or Binary Expression (Char,Nchar,Nvarchar,Varchar,BinaryOrVarbinary) When the expression is converted to a different data type, the data may be truncated and only a portion of the data is displayed, or an error is returned because the result is too short to be displayed. Except for the conversions shown in the following tableChar,Varchar,Nchar,Nvarchar,BinaryAndVarbinaryWill be truncated.

Converted data type	Converted to Data Type	Result
Int,SmallintOrTinyint	Char	*
	Varchar	*
	Nchar	E
	Nvarchar	E
Money,Smallmoney,Numeric,Decimal,FloatOrReal	Char	E
	Varchar	E
	Nchar	E
	Nvarchar	E

 

* The result cannot be displayed because it is too short.
E. An error is returned because the result length is too short to be displayed.

Microsoft SQL Server only ensures round-trip conversion (that is, conversion from the original data type and return the original data type) to produce the same value between versions. The following example shows round-trip conversion:

DECLARE @myval decimal (5, 2)SET @myval = 193.57SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))-- Or, using CONVERTSELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))

For example, do not try to constructBinaryValues and convert them to numeric data types. SQL Server does not guaranteeDecimalOrNumericConvert data typeBinaryThe results are the same in SQL Server versions.

The following example shows the result expression that cannot be displayed because it is too short.

USE pubsSELECT SUBSTRING(title, 1, 25) AS Title, CAST(ytd_sales AS char(2))FROM titlesWHERE type = 'trad_cook'

The following is the result set:

Title                        ------------------------- -- Onions, Leeks, and Garlic *  Fifty Years in Buckingham *  Sushi, Anyone?            *  (3 row(s) affected)

When data types with different decimal places are converted, the value is truncated to the most precise digit. For example, select cast (10.6496Int.

If the number of decimal places of the target data type is smaller than the number of decimal places of the source data type, the value to be converted is rounded down. For example, cast (10.3496847Money) Is $10.3497.

When a non-numeric typeChar,Nchar,VarcharOrNvarcharConvert dataInt,Float,NumericOrDecimalSQL Server Returns an error message. When you convert an empty string ("")NumericOrDecimalSQL Server also returns an error message.

Use binary string data

WhenBinaryOrVarbinaryWhen the data is converted to character data and the odd digit value is specified after X, SQL Server adds 0 (zero) to the end of X to become an even number.

Binary data contains characters ranging from 0 to 9 and from A to F (or from A to F), each of which is a group. The binary string must start with 0x. For example, to enter ff, type 0xff. The maximum value is a binary value of 8000 bytes, and the maximum value of each byte is ff.BinaryThe data type cannot be used for hexadecimal data, but for bit mode. The accuracy of hexadecimal numbers stored as binary data cannot be guaranteed.

WhenBinaryThe length of each two characters in a data type is counted as a unit length. The length is 10, indicating that 10 double character groups will be entered.

Null binary strings represented by 0x can be stored as binary data.

Example A. Use both cast and convert

In each example, the name of the book will be retrieved (the first digit of the current sales volume of these books is 3), andYtd_salesConvertChar (20).

-- Use CAST.USE pubsGOSELECT SUBSTRING(title, 1, 30) AS Title, ytd_salesFROM titlesWHERE CAST(ytd_sales AS char(20)) LIKE '3%'GO-- Use CONVERT.USE pubsGOSELECT SUBSTRING(title, 1, 30) AS Title, ytd_salesFROM titlesWHERE CONVERT(char(20), ytd_sales) LIKE '3%'GO

The following is the result set of any query:

Title                          ytd_sales   ------------------------------ ----------- Cooking with Computers: Surrep 3876        Computer Phobic AND Non-Phobic 375         Emotional Security: A New Algo 3336        Onions, Leeks, and Garlic: Coo 375         (4 row(s) affected)

B. Use cast with Arithmetic Operators

The following example uses (Ytd_sales) And the price of each book (Price) To separate columns (Copies). After rounding to the nearest integer, the result is convertedIntData type.

USE pubsGOSELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS 'Copies'FROM titlesGO

The following is the result set:

Copies      ------ 205         324         6262        205         102         7440        NULL        383         205         NULL        17          187         16          204         418         18          1263        273         (18 row(s) affected)

C. Use cast for concatenation

The following example uses the cast data type conversion function to concatenate non-character and non-binary expressions.

USE pubsGOSELECT 'The price is ' + CAST(price AS varchar(12))FROM titlesWHERE price > 10.00GO

The following is the result set:

------------------ The price is 19.99        The price is 11.95        The price is 19.99        The price is 19.99        The price is 22.95        The price is 20.00        The price is 21.59        The price is 10.95        The price is 19.99        The price is 20.95        The price is 11.95        The price is 14.99        (12 row(s) affected)

D. Use cast to obtain more readable texts.

The following example uses castTitleColumn conversionChar (50)Column to make the results easier to read.

USE pubsGOSELECT CAST(title AS char(50)), ytd_salesFROM titlesWHERE type = 'trad_cook'GO

The following is the result set:

                                                       ytd_sales--------------------------------------------------     ---------Onions, Leeks, and Garlic: Cooking Secrets of the      375Fifty Years in Buckingham Palace Kitchens              15096Sushi, Anyone?                                         4095(3 row(s) affected)

E. Use the cast with the like clause

In the following exampleIntColumn (Ytd_salesColumn)Char (20)Column to use the like clause.

USE pubsGOSELECT title, ytd_salesFROM titlesWHERE CAST(ytd_sales AS char(20)) LIKE '15%'   AND type = 'trad_cook'GO

The following is the result set:

title                                                        ytd_sales   ------------------------------------------------------------ ----------- Fifty Years in Buckingham Palace Kitchens                    15096       (1 row(s) affected)