Conversions between Character and Binary Values in Katmai

Prior to Microsoft SQL Server 2008, when you used the CONVERT function to convert a character string to a binary type, you got the ASCII codes of the characters. Similarly, when converting from a binary value to a character string, you got the characters represented by the ASCII codes in the binary value.

If you had character strings representing hex digits in a binary value (e.g., '0x53514C'), and wanted to produce binary values that look the same (0x53514C), or the other way around, you had to use custom convoluted solutions. Such types of conversions are required for tasks such as importing data.

Microsoft SQL Server 2008 provides a nifty solution to this need by introducing new styles for the CONVERT function when converting between character strings and binary values. When using style 0, you get the default behavior as in previous versions of Microsoft SQL Server. For example, run the following code:

Styles 1 and 2 introduce the new functionality. Style 1 includes the '0x' prefix in the result string when converting a binary value to a character string, and requires it in the input string when converting from a string to a binary value. Style 2 does not include the '0x' prefix in the result string when converting a binary value to a character string, and does not require it in the input string when converting from a string to a binary value. To demonstrate the new styles, run the following code:

From the Blogs

Many organizations today cannot use public cloud solutions because of security concerns, administrative challenges and functional limitations. However, they still need a centralized platform where end users can conduct self-service analytics in an IT-enabled environment....More

It is crucial to move away from data and analytics stored on individual desktop computers. Today’s solutions must promote holistic, collective intelligence. The strong, continued alliance between Microsoft and Pyramid Analytics helps make all this possible....More

To become a truly data-driven enterprise, many business leaders recognize that they must extend the capabilities of self-service business intelligence (BI) and analytics to more of their business users. Many BI tools tackle part of this need, but they don’t offer a complete enterprise solution....More