SQL - CONVERT

CONVERT()

The CONVERT() function converts a value (of any type) into a specified datatype.

Using CONVERT() with conversion styles

The CAST() function converts a value (of any type) into a specified datatype.

 Syntax for CAST:

CAST ( expression AS data_type [ (length ) ])

However CONVERT(), as opposed to CAST(), allows you to specify a conversion style, which defines additional formatting rules when converting. This mostly applies to date conversions where you may want to convert a date column to/from a specific format, but it also comes in handy when converting binary values.

Syntax for CONVERT:

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

data_type	Required. The datatype to convert expression to. Can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image
(length)	Optional. The length of the resulting data type (for char, varchar, nchar, nvarchar, binary and varbinary)
expression	Required. The value to convert to another data type
style	Optional. The format used to convert between data types, such as a date or string format.

Consider the following:

Example 1 (Binary vs Binary)

DECLARE @s varchar(100)='0x48656C6C6F';

SELECT CONVERT(varbinary(max), @s);     --- 0x307834383635364336433646

Because the value I want to convert is a string value, that string value is literally converted to a binary value, so the leading “0x” becomes 0x3078, and so on. What we really wanted to do was just to change the datatype. To remedy this, we can add a conversion style to the CONVERT():

DECLARE @s1 varchar(100)='0x48656C6C6F';

SELECT CONVERT(varbinary(max), @s1, 1);  --- 0x48656C6C6F

The “, 1” style tells SQL Server that you don’t want to convert the string to its corresponding ASCII codes, but rather treat the string as a hex string in text form. You could specify “, 2” if you wanted to skip the “0x” part as well.

This conversion style option is great when you’re accepting hex values as arguments to a function or stored procedure, provided in a plain-text char/varchar variable.

Example 2 (Binary vs Varchar)

For example, suppose you want to convert the following binary value to hexadecimal string: 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00. To do that, you use the CONVERT function with binary styles as follows:

SET NOCOUNT ON;

DECLARE @BinaryValue VARBINARY(256) 

SET @BinaryValue = 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00

SELECT  CONVERT([varchar](512), @BinaryValue, 1)

SELECT  CONVERT([varchar](512), @BinaryValue, 2) 

SET NOCOUNT OFF;

Results 1:

0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00

Results 2:

0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00

Random Example :  (Binary vs Hex)

9B5367E0      -1689032736

3BFFFFFF      1006632959

Select convert(VARBINARY, '9B5367E0', 2) as AfterBinary

Select convert(int, 0x9B5367E0 ) as AfterBinary 

Select convert(int, convert(VARBINARY, '9B5367E0', 2)) as AfterBinary

Select convert(VARBINARY, '3BFFFFFF', 2) as AfterBinary

Select convert(int, 0x3BFFFFFF ) as AfterBinary 

Select convert(int, convert(VARBINARY, '3BFFFFFF', 2)) as AfterBinary

Select convert(VARBINARY, '3BFFFFFF') as AfterBinary

Select convert(VARBINARY, '3BFFFFFF', 2) as AfterBinary

Select convert(int, 0x3342464646464646 ) as AfterBinary 

Select convert(int, convert(VARBINARY, '3BFFFFFF')) as AfterBinary

Select convert(int, convert(VARBINARY, '3BFFFFFF', 2)) as AfterBinary

Select convert(VARBINARY, '9B5367E0', 2) as AfterBinary

Select convert(VARBINARY, '9B5367E0') as AfterBinary

Select convert(int, convert(VARBINARY, '9B5367E0', 2)) as AfterBinary

Select convert(int, convert(VARBINARY, '9B5367E0')) as AfterBinary

Select Cast('9B5367E0' As VARBINARY(1)) as AfterBinary

Select Cast('9B5367E0' As VARBINARY(2)) as AfterBinary

Select Cast('9B5367E0' As VARBINARY(10)) as AfterBinary

Select Cast(0x3942353336374530 As int) as AfterBinary

Select convert(int, 0x39 )as AfterBinary

Select convert(int, 0x3942 )as AfterBinary