We have seen a number of string manipulation examples using XML. Here is yet another example that uses FOR XML to convert a VARBINARY value to VARCHAR.

Some one asked me this question in one of the forums. The story is like this. The OP did a data migration project and the original data from the ORACLE database is imported to a SQL Server database. After the migration, he noticed that one of the VARCHAR columns in the source database is imported as VARBINARY column in SQL Server. Now he wanted to convert the VARBINARY value back to VARCHAR.

Here is a simple example that demonstrates the case.

DECLARE @x VARBINARY(10)
SELECT  @x = CAST('10' as VARBINARY(10))
SELECT @x AS VarBinaryValue

/*
VarBinaryValue
----------------------
0x3130
*/

The original value was ‘10’ which was converted to VARBINARY and the result is ‘0x3130’. We need to write a query to convert ‘0x3130’ back to ‘10’.

I could not find an easy method to achieve this. I wrote a quick and dirty piece of code using FOR XML to achieve this.

DECLARE @x VARBINARY(10)
SELECT  @x = CAST('10' as VARBINARY(10))
SELECT @x AS VarBinaryValue

/*
VarBinaryValue
----------------------
0x3130
*/

SELECT (
    SELECT 
        CHAR(SUBSTRING(@x,number,1)) AS 'text()'
    FROM master..spt_values 
    WHERE type = 'P'
        AND Number BETWEEN 1 AND LEN(@x)    
    FOR XML PATH('')
) AS TextValue

/*
TextValue
-------------------------------------------
10
*/

Update on 14 June 2009

RBarry commented that this can be achieved by a simple CAST back to VARCHAR. Yes, it works! I wonder what went wrong when I tested it initially and could not get back the original value. Anyway, the XML approach is not needed any more. A simple cast will do the trick as shown in the below example.

DECLARE @x VARBINARY(10)
SELECT  @x = CAST('10' as VARBINARY(10))

SELECT 
    @x AS VarBinaryValue,
    CAST(@x AS VARCHAR(10)) AS VarcharValue

/*
VarBinaryValue         VarcharValue
---------------------- ------------------------------
0x3130                 10
*/

FOR XML Tutorials