Detailed explanation of sql cast and convert usage

Detailed explanation of sql cast and convert usage

Summary:

The following is only applicable to mysql

SELECT {fn CONCAT(CONVERT(user_id,CHAR),USER_NAME)} AS str FROM t_sys_user

The following is only applicable In sqlserver2008

SELECT {fn CONCAT(CONVERT(CHAR, user_id),USER_NAME)} AS str FROM t_sys_user

The following sqlserver is compatible with mysql

SELECT {fn CONCAT(CAST(user_id AS CHAR),USER_NAME)} AS str FROM t_sys_user

Note: cast is a type conversion function, common to sqlserver and mysql.是 Convert is also a type conversion, which exists in SQLServer2008 and MySQL, but the parameter order is the opposite.数 Concat function is found in SQLServer2008 and MySQL, but it is necessary to apply this in SQLServer2008 {Fn Concat (ARG0, ARG1, ...)} and not applicable to connecting with int and Varchar type. In sqlserver2008, string connections generally use the "+" sign. However, it seems that direct use of the concat function is supported in sqlserver2012.

The type restrictions that mysql can be converted into:

are CAST (xxx AS type), CONVERT (xxx, type).

The types that can be converted are limited. This type can be one of the following values:

Binary, with the effect of binary prefix: BINARY

Character type, can take parameters: CHAR()

Date: DATE

Time: TIME

Date time type: DATETIME

Floating point number: DECIMAL

Integer: SIGNED

Unsigned integer: UNSIGNED

The following part is reproduced and the above part is summarized by myself.

sqlserver

Explicitly convert an expression of one data type to another data type. CAST and CONVERT provide similar functionality.

Syntax

Using CAST:

CAST (expression AS data_type)

Using CONVERT:

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

Parameters

expression

is any valid Microsoft® SQL Server™ expressions. See Expressions for more information.

data_type

The data type provided by the target system, including bigint and sql_variant. User-defined data types cannot be used. For more information about the available data types, see Data Types.

length

Optional argument for nchar, nvarchar, char, varchar, binary or varbinary data type.

style

Date formatting style, which converts datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar or nvarchar data types); or string formatting style, which converts float, real, money or smallmoney Data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

SQL Server supports data formats in Arabic style using Kuwaiti algorithm.

In the table, the two columns on the left represent the style values ​​that convert datetime or smalldatetime to character data. Add 100 to the style value to obtain a four-digit year (yyyy) including the century digit.

Without century digit (yy) With century digit (yyyy)

Standard

Input/Output**

- 0 or 100 (*) Default value mon dd yyyy hh:miAM (or PM)

1 101 United States mm/dd/yyyy

2 102 ANSI yy.mm.dd

3 103 United Kingdom/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 + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)

10 110 US mm-dd-yy

11 111 Japan yy/mm/dd

12 112 ISO yymmdd

- 13 or 113 (*) European default + milliseconds dd mon yyyy hh:mm:ss :mmm(24h)

14 114 - hh:mi:ss:mmm(24h)

- 20 or 120 (*) ODBC specification yyyy-mm-dd hh:mm:ss[.fff]

- 21 or 121 (*) ODBC specification (with milliseconds) yyyy-mm-dd hh:mm:ss[.fff]

- 126 (***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm (without spaces )

- 130* Kuwait dd mon yyyy hh:mi:ss:mmmAM

- 131* Kuwait

Default value (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, 21 or 121) always Returns the century digit (yyyy).

** Input when converted to datetime; output when converted to character data.

*** Specifically for XML. For conversion from datetime or smalldatetime to character data, the output format is as shown in the table. For conversions from float, money, or smallmoney to character data, the output is equivalent to style 2. For conversions from real to character data, the output is equivalent to style 1.

Important By default, SQL Server interprets two-digit years based on the cutoff year 2049. That is, a two-digit year 49 is interpreted as 2049, and a two-digit year 50 is interpreted as 1950. Many client applications, such as those based on OLE Automation objects, use 2030 as the cutoff year. SQL Server provides a configuration option ("Two-digit cut-off year") that allows you to change the cut-off year used by SQL Server and treat dates consistently. The safest approach, however, is to specify a four-digit year.

When converting from smalldatetime to character data, styles containing seconds or milliseconds will display zeros in these positions. When converting from datetime or smalldatetime values, you can truncate unwanted date parts by using the appropriate char or varchar data type length.

The table below shows style values ​​when converting from float or real to character data.

Value Output

0 (default) Maximum 6 digits. Use scientific notation as appropriate.

1 is always an 8-bit value. Always use scientific notation.

2 is always a 16-bit value. Always use scientific notation.

In the table below, the left column represents the style value when converting from money or smallmoney to character data.

Value Output

0 (default value) Each three digits on the left side of the decimal point are not separated by commas, and two digits are taken on the right side of the decimal point, for example, 4235.98.

1 Separate every three digits to the left of the decimal point with commas, and take two digits to the right of the decimal point, for example, 3,510.92.

2 Each three-digit number on the left side of the decimal point is not separated by a comma, and four digits are taken on the right side of the decimal point, for example, 4235.9819.

Return Type

Returns the same value as data type 0.

Notes

Implicit conversions refer to those conversions that do not specify the CAST or CONVERT function. Explicit conversions, on the other hand, are those for which the required CAST (CONVERT) function has been specified. The following chart shows all the explicit and implicit conversions available for the data types provided by the SQL Server system, including bigint and sql_variant.

Description Because Unicode data always uses an even number of bytes, hints are used when converting between binary or varbinary data types and data types supported by Unicode. For example, instead of returning the hexadecimal value of 41, this conversion returns the hexadecimal value of 4100: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary)

Automatic conversion of text and image data types is not supported Data type conversion. You can explicitly convert text data to character data and image data to binary or varbinary data, but the maximum length is 8000. SQL Server produces an error message if an incorrect conversion is attempted (for example, a character expression containing letters is converted to an int).

When the output of CAST or CONVERT is a string and the input is also a string, the output has the same collation and collation label as the input. If the input is not a string, the output uses the database's default collation and the mandatory default collation label. For more information, see Collation Precedence.

To assign a different collation 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 implicit conversion from the sql_variant data type regarding assignment, but there is an implicit conversion to sql_variant.

When converting a character or binary expression (char, nchar, nvarchar, varchar, binary or varbinary) to an expression of a different data type, the data may be truncated, only partially displayed, or returned because the result is too short to display mistake. In addition to the conversions shown in the following table, conversions to char, varchar, nchar, nvarchar, binary, and varbinary will be truncated.

varchar *

nchar E

nvarchar E

money, smallmoney, numeric, decimal, float or real char E

varchar E

nchar E

nvarchar E

* The result length is too short to show.

E An error is returned because the result length is too short to display.

Microsoft SQL Server only guarantees that round-trip conversions (that is, conversions from and back to the original data type) produce the same value across versions. The following example shows a 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 construct binary values ​​and convert them to data types classified as numeric data types. SQL Server does not guarantee that the result of converting a decimal or numeric data type to binary will be the same across versions of SQL Server.

The example below shows a resulting expression that is too short to display.

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 with different decimals When converting to a data type with a number of digits, the value is truncated to the nearest digit. For example, SELECT CAST(10.6496 AS int) returns 10.

When converting, if the number of decimal places of the target data type is less than the number of decimal places of the source data type, the value to be converted will be rounded off. For example, the result of CAST(10.3496847 AS money) is $10.3497.

SQL Server returns an error message when converting non-numeric type char, nchar, varchar or nvarchar data to int, float, numeric or decimal. SQL Server also returns an error message when converting an empty string (" ") to numeric or decimal.

Using Binary String Data

When binary or varbinary data is converted to character data and an odd-digit value is specified after x, SQL Server adds 0 (zero) after x to make it an even-digit value.

Binary data contains characters from 0 to 9 and from A to F (or from a to f), in groups of two characters. Binary strings must start with 0x. For example, to enter FF, type 0xFF. The maximum value is an 8000-byte binary value, with each byte having a maximum value of FF. The Binary data type cannot be used for hexadecimal data, but for bit patterns. The accuracy of conversion and calculation results of hexadecimal numbers stored as binary data cannot be guaranteed.

When specifying the length of binary data type, every two characters are counted as one unit length. A length of 10 means that 10 bigrams will be entered.

The empty binary string represented by 0x can be stored as binary data.

Example

A. Using both CAST and CONVERT

Each example will retrieve the book titles (the first digit of the current sales of these books is 3) and convert the ytd_sales of these books to char(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. Using CAST with arithmetic operators

The following example compares the total to current sales (ytd_sales) with each Divide the price of the book (price) and perform a separate column calculation (Copies). This result is converted to the int data type after rounding to the nearest integer.

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

The following is the result set:

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

C . Concatenation using CAST

The following example uses the CAST data type conversion function to concatenate non-character, 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 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 get more readable text

The example below uses CAST in the select list to convert the title column to a char(50) column so the result will be more readable.

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 CAST with LIKE clause

The following example converts int column (ytd_sales column) to 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 ------------------------------------------------- -----------

Mysql:

MySQL's CAST() and CONVERT() functions can be used to obtain a value of one type and generate a value of another type. The specific syntax of the two is as follows:

1 CAST (value as type);

2 CONVERT (value, type);

is CAST (xxx AS type), CONVERT (xxx, type).

The types that can be converted are limited. This type can be one of the following values:

Binary, with the effect of binary prefix: BINARY

Character type, can take parameters: CHAR()

Date: DATE

Time: TIME

Date time type: DATETIME

Floating point: DECIMAL

Integer: SIGNED

Unsigned integer: UNSIGNED Here are a few examples:

Example 1

1 mysql> SELECTCONVERT('23',SIGNED );

2 +- ---------------------+

3 | CONVERT('23',SIGNED) |

4 +---------- ----------+

5 | 23 |

6 +---------------------+

7 1 row inset

Example 2

1 mysql> SELECTCAST('125e342.83'ASsigned);

2 +-------------------------- ----+

3 | CAST('125e342.83'ASsigned) |

4 +-------------------------- ---+

5 | 125 |

6 +--------------------------------+

7 1 row inset

Example 3

1 mysql> SELECTCAST('3.35'ASsigned);

2 +------------------------+

3 | CAST('3.35'ASsigned) |

4 +------------------------+

5 | -----------------------+

7 1 row inset

Like the above example, convert varchar to int using cast(a as signed), where a is a string of varchar type.

Example 4

In SQL Server, the following code demonstrates the hexadecimal storage result of date storage when the datetime variable contains only a simple date and a simple time.

01 DECLARE @dt datetime

02

03 -- Simple date

04 SET @dt='1900-1-2'

05 SELECT CAST(@dt asbinary(8))

06 -- Result: 0x0000000100000000

07

08 --Simple time

09 SET @dt='00:00:01'

10 SELECT CAST(@dt asbinary(8))

11 --Result: 0x0000000000 00012C

MySQL's type conversion is the same as that of SQL Server, except that the type parameters are a little different: CAST(xxx AS type), CONVERT(xxx, type).