Go: SQL cast and convert usage explained

Original: http://www.2cto.com/database/201310/250880.html

SQL cast and CONVERT usage detailed   Summary: The following applies only to Mysqlselect {fn CONCAT (CONVERT (User_id,char), user_name)} as Str from T_SYS_ User following this applies only to Sqlserver2008select {fn CONCAT (CONVERT (char,user_id), user_name)} as Str from T_SYS_ User the following SQL Server and MySQL compatible select {fn CONCAT (CAST (user_id as CHAR), user_name)} as Str from t_sys_user  Note: Cast is a type conversion function, SQL Server and MySQL are generic.             Convert is also a type conversion, which exists in both sqlserver2008 and MySQL, but the parameter order is reversed.             CONCAT functions are available in both sqlserver2008 and MySQL, but in sqlserver2008 it is necessary to apply the {FN concat (arg0,arg1 ,.....)} And does not apply to connections to type int and varchar type. The "+" number is generally used for string connections in sqlserver2008. However, it seems that the CONCAT function is supported directly in sqlserver2012.  mysql can be converted into a type restriction: cast (XXX as type), convert (XXX, type). There is a limit to the types that can be converted. This type can be one of the following values: binary, with binary prefix effect: binary     character type, with parameters: CHAR ()      Date: Date      Hours: Time      datetime: Date      floating-point number: DECIMAL       integer: Signed   &nbsp ;  unsigned integer: unsigned   is divided into the lower partReproduced in the above section for their own summary.  sqlserver explicitly converts an expression of a 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]) parameter expression is any valid The Microsoft®sql Server™ expression. For more information, see expressions. Data_type the data types provided by the target system, including bigint and sql_variant. You cannot use a user-defined data type. For more information about the available data types, see Data types. Optional parameters for Lengthnchar, nvarchar, char, varchar, binary, or varbinary data types. Style date format style, whereby DateTime or smalldatetime data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data type), or a string format style that will 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 Arabian styles using the 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 get a four-bit year (yyyy) that includes century digits. No century digits (yy) with century digits (yyyy)   standard   input/output ** -0 or (*)   default mon dd yyyy hh:miam (or PM)  1 101 US mm/dd/y YYY 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 + MS Mon dd yyyy hh:mi:ss:mmmAM (or PM)  10 110 US mm-dd-yy 11 111 Japan yy/mm/ dd 12 ISO yymmdd -13 or 113 (*)   European default + MS DD Mon yyyy hh:mm:ss:mmm (24h)  14 114-hh:mi:ss:mmm (24 h)  -20 or (*)  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 (excluding spaces)  -130* Kuwait DD Mon yyyy hh:mi:ss:mmmam -131* Kuwait &NB Sp The default value (style 0 or 100, 9 or 109, 13 or 113, 20, or 120, 21, or 121) always returns century digits (yyyy). * * input when converting to datetime, and output when converting to character data. specifically for XML. For conversions from DateTime or smalldatetime to character data, the output format is 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, the two-digit year 49 is interpreted as 2049, and the 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 cutoff year") to change the cutoff year used by SQL Server and to make the dates consistent. The safest option, however, is to specify a four-digit numberYear.   When converting from smalldatetime to character data, styles containing seconds or milliseconds will display zeros at these locations. When converting from a datetime or smalldatetime value, you can truncate the unwanted date part by using the appropriate char or varchar data type length. The following table shows the style values when converting from float or real to character data. The value output  0 (default) is a maximum of 6 digits. Use scientific notation as needed.  1 is always a 8-bit value. Always use scientific notation.  2 is always a 16-bit value. Always use scientific notation.   In the following table, left column represents the style value when converting from money or smallmoney to character data. Value output  0 (default) every three digits to the left of the decimal point is not separated by commas, and two digits to the right of the decimal point, for example 4235.98.  1 each three digits to the left of the decimal point is separated by commas and two digits to the right of the decimal point, such as 3,510.92.  2 each three digits to the left of the decimal point is not separated by commas, and four digits to the right of the decimal point, for example 4235.9819. The    return type returns the same value as data type 0. Note Implicit conversions are those that do not specify a cast or CONVERT function. Explicit conversions refer to those transformations that have the required cast (convert) function specified. The following diagram shows all explicit and implicit transformations that are available for the data types provided by the SQL Server system, including bigint and sql_variant.    description   Because Unicode data always uses even-bit bytes, hints are used when converting between binary or varbinary data types and data types supported by Unicode. For example, instead of returning a hexadecimal value of 41, this conversion returns a hexadecimal value of 4100: SELECT cast (0x41 as nvarchar) as varbinary   does not support automatic data type conversions for text and image data types. The text data can be explicitly converted to character data, and the image data is dominant to binary or varbinary data, but the maximum length is 8000. If you attempt an incorrect conversion (for example, to convert a character expression that contains letters to an int), the SQL ServerAn error message is generated. 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 takes the default collation of the database and enforces the default collation label. For more information, see Precedence Order of collations. 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 of the assignment from the sql_variant data type, but there is a covert conversion to sql_variant. When you convert 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, displayed only as part, or returned with an error because the result is too short to display. In addition to the conversions shown in the following table, conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated.  varchar *   nchar e   nvarchar E money, smallmoney, numeric, decimal, float or real char e& nbsp;  varchar e   nchar e   nvarchar e * result length is too short to display. E returns an error because the result length is too short to display. Microsoft SQL Server only guarantees a round-trip conversion (that is, after converting from the original data type and returning the original data type) to 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) as decimal (10,5))-- Or, using Convertselect convert (decimal (10,5), convert (varbinary), @myval) For example, do not attempt 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 decimal or numeric data types to binary is the same between versions of SQL Server. The following example shows a result expression that cannot be displayed because it is too short. Use Pubsselect SUBSTRING (title, 1, +) as title, CAST (ytd_sales as char (2)) from Titleswhere type = "Trad_cook" is the result set: Tit Le                        ---------------------------onions, Leeks, and Garlic *  fifty years in Buckingham *  sushi, anyone?            *   (3 row (s) affected) when a data type with different decimal digits is converted, the value is truncated to the most accurate digit. For example, the result of SELECT CAST (10.6496 as int) is 10. When converting, the value to be converted is rounded if the number of decimal digits of the target data type is less than the number of decimal digits of the source data type. For example, the result of CAST (10.3496847 as Money) is $10.3497. When converting char, nchar, varchar, or nvarchar data of non-numeric types to int, float, numeric, or decimal, SQL Server returns an error message. When you convert an empty string ("") to numeric or decimal, SQL Server also returns an error message. Using binary string data when binary or varbinary data is converted to character data and the value of an odd bit is specified after X, SQL Server adds 0 (0) after x to become an even digit value. Binary data contains characters from 0 to 9 and from a to f (or from A to f), each of which is a group of two characters. Binary strings must start with 0x. For example, to enter FF, type 0xFF. The maximum value is a 8000-byte binary value, and the maximum value for each byte is FF. BinaThe RY data type cannot be used for hexadecimal data, but for bit patterns. The conversion and calculation results of hexadecimal numbers stored as binary data cannot be guaranteed to be accurate. When you specify the length of the binary data type, every two characters are counted as one unit length. A length of 10 indicates that 10 double-character groups will be entered. A null binary string represented by 0x can be stored as binary data. Example A. Using both CAST and convert each example retrieves the title (the first digit of the book's current sales is 3) and converts the ytd_sales of those books to char (20). -Use CAST. Use Pubsgoselect SUBSTRING (title, 1, +) as title, Ytd_salesfrom Titleswhere CAST (ytd_sales as char) like ' 3% ' go--U Se CONVERT. Use Pubsgoselect SUBSTRING (title, 1, +) as title, Ytd_salesfrom titleswhere CONVERT (char (), ytd_sales) like ' 3% ' go below Result set for either query: Title                          ytd_sales   -----------------------------------------Cooking with Computers:surrep 3876        computer phobic Non-phobic 375         emotional security:a New Algo 3336        onions, Lee KS, and Garlic:coo 375         (4 row (s) affected) B. Using cast with arithmetic operators The following example uses the total cutoff current sales (ytd_sales) with each The price of the book Divide and perform a separate column calculation (Copies). After rounding to the nearest integer, the result is converted to the int data type. Use Pubsgoselect CAST (ROUND (ytd_sales/price, 0) as int) as "Copies" from Titlesgo below is the result set: Copies      ----- -205         324         6262        205         102         7440        null        383         205         NULL        17          187 &NB Sp                204         418                  1263        273         (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) from Titleswhere price > 10.00GO Below is the result set:----------------- -The price is 19.99        the Price are 11.95        the price are 19.99        the Price is 19.99 &nbs P      the Price was 22.95        the Price was 20.00        the Price is 21.59        the price are 10.95        the Price is 19.99        the Price are 20.95        the price are 11.95        the Price is 14.99 &nbsp ;       (s) affected D. Using cast for more readable text The following example uses cast to convert the title column to a char (50) column in the select list, which makes the result easier to read. Use Pubsgoselect CAST (title as char), ytd_salesfrom titleswhere type = ' Trad_cook ' Go below is the result set:      &NBS P                          ,         &NB Sp            ytd_sales--------------------------------------------------   --- ------onions, leeks, and GArlic:cooking Secrets of the      375fifty years in Buckingham Palace Kitchens              15096sushi, anyone?                          ,         &NB Sp     4095 (3 row (s) affected) E. Use the following example cast with the LIKE clause to convert the INT column (ytd_sales column) to a char (20) column to use the LIKE clause. Use Pubsgoselect title,-ytd_salesfrom titleswhere CAST (ytd_sales as char) like ' 15% ' and type = ' Trad_cook ' go&nbsp ; The following is the result set: Title    ytd_sales  ------------------------------------------------------------  The cast () and CONVERT () functions of the mysql: mysql can be used to get a value of one type and produce a value of another type. The specific syntax for both is as follows: 1CAST (value as type);2CONVERT (value, type);is cast (xxx as type), CONVERT (XXX, type). There is a limit to the types that can be converted. This type can be one of the following values: binary, with binary prefix effect: binary character type, parameter: CHAR () Date: Date: Time datetime type: DATETIME floating-point number: DECIMAL integer: Signed unsigned integer: UNSIGNED Here are a few examples: example 11mysql> Selectconvert (' Up ', signed);2 +----------------------+3 | CONVERT (' All ', signed) |4 +----------------------+5 | |6 +----------------------+71 Row InsetExample 21mysql> selectcast (' 125e342.83 ' ASsigned);2 +------------------------------+3 | CAST (' 125e342.83 ' ASsigned) |4 +------------------------------+5 | |6 +------------------------------+71 Row InsetExample 31mysql> selectcast (' 3.35 ' ASsigned);2 +------------------------+3 | CAST (' 3.35 ' ASsigned) |4 +------------------------+5 | 3 |6 +------------------------+71 Row InsetAs in the example above, the varchar is converted to int with cast (a as signed), where a is a varchar-type string. Example 4 in SQL Server, the following code demonstrates the result of a DateTime variable that contains only the pure date and the pure time when the date stores the hexadecimal store representation. 01DECLARE @dt datetime0203--Simple date04SET @dt = ' 1900-1-2 '05SELECT CAST (@dt asbinary (8))06--Result: 0x00000001000000000708--Simple Time09SET @dt = ' 00:00:01 '10SELECT CAST (@dt asbinary (8))11--Result: 0x000000000000012cMySQL type conversion is like SQL Server, that is, the type parameter is a little different: CAST (xxx as type), convert (XXX, type).

Go: SQL cast and convert usage explained