Converting from hex string to varbinary and vice versa

Converting hexadecimal values to varbinary and vice versa is now easier using the XQuery functionality available from SQL Server 2005. The code samples below show how to perform the conversion(s): — Convert hexstring value in a variable to varbinary: declare @hexstring varchar(max); set @hexstring = ‘abcedf012439’; select cast(” as xml).value(‘xs:hexBinary( substring(sql:variable("@hexstring"), sql:column("t.pos")) )’, ‘varbinary(max)’) from…

5

Converting from Base64 to varbinary and vice versa

Converting Base64 values to varbinary and vice versa is now easier using the XQuery functionality available from SQL Server 2005 onwards. The code samples below show how to perform the conversion: — Convert Base64 value in a variable to varbinary: declare @str varchar(20); set @str = ‘3qAAAA==’; select cast(N” as xml).value(‘xs:base64Binary(sql:variable("@str"))’, ‘varbinary(20)’); — Convert binary…

2

Differences between ISNULL and COALESCE

I came across a question in the SQL Server MVP newsgroup recently about ISNULL and COALESCE usage. COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine. Both ISNULL and COALESCE can be used to get the same results but there are some differences. 1. Data type determination of…

8