Convert VARBINARY to NVARCHAR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Convert VARBINARY to NVARCHAR

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-03-12 : 16:08:25

Can we convert varbinary data into nvarchar?

declare @tbl1 table
(ID INT,
Field1 VARBINARY(8000),
Field2 NVARCHAR(255)
)

INSERT INTO @tbl1
SELECT 1, 0x41B207FE64F1643AD6E56A5DB2550D7E, null

INSERT INTO @tbl1
SELECT 2,0xEAE6AFC34526384B7537421591170371,null

INSERT INTO @tbl1
SELECT 3,0xA550D55692A3FBB84775A024ADC5A837,null

INSERT INTO @tbl1
SELECT 4, 0x573D2E533595DB65FE53063CC4FC7451,null

INSERT INTO @tbl1
SELECT 5,0xA1DDEE3A67C6DCC04C45D7E625BCAE5F,null

UPDATE @tbl1
SET Field2 = CAST(Field1 AS NVARCHAR(MAX))

SELECT *
FROM @tbl1

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-03-12 : 16:18:46

What problem are you having? You are casting to nvarchar(MAX) but your column is only 255 characters.

=======================================
There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980)

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-03-12 : 16:32:36

declare @tbl1 table
(ID INT,
Field1 VARBINARY(8000),
Field2 NVARCHAR(max)
)

INSERT INTO @tbl1
SELECT 1, 0x41B207FE64F1643AD6E56A5DB2550D7E, null

INSERT INTO @tbl1
SELECT 2,0xEAE6AFC34526384B7537421591170371,null

INSERT INTO @tbl1
SELECT 3,0xA550D55692A3FBB84775A024ADC5A837,null

INSERT INTO @tbl1
SELECT 4, 0x573D2E533595DB65FE53063CC4FC7451,null

INSERT INTO @tbl1
SELECT 5,0xA1DDEE3A67C6DCC04C45D7E625BCAE5F,null

UPDATE @tbl1
SET Field2 = CAST(Field1 AS NVARCHAR(max))

SELECT *
FROM @tbl1

Output like this :
1 0x41B207FE64F1643AD6E56A5DB2550D7E 0x41B207FE64F1643AD6E56A5DB2550D7E
2 0xEAE6AFC34526384B7537421591170371 0xEAE6AFC34526384B7537421591170371
3 0xA550D55692A3FBB84775A024ADC5A837 0xA550D55692A3FBB84775A024ADC5A837
4 0x573D2E533595DB65FE53063CC4FC7451 0x573D2E533595DB65FE53063CC4FC7451
5 0xA1DDEE3A67C6DCC04C45D7E625BCAE5F 0xA1DDEE3A67C6DCC04C45D7E625BCAE5F

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-03-12 : 17:27:28

CONVERT(NVARCHAR(max), Field1, 1)

=======================================
There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-12 : 18:13:24

quote:
Originally posted by Bustaz Kool

CONVERT(NVARCHAR(max), Field1, 1)

I think he wants the hex vale (0x412333BF...) which is stored as a VARCAHR to be displayed back as 0x412333BF... but as a string and not a binary value.

I think you need to use a BINARY to STRING user defined function in order to accomplish that.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-03-12 : 18:37:49

I looked up the CONVERT in BOL and was able to get the string generated by using the "1" option. Are you are getting different results?

=======================================
There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-12 : 19:00:53

Ahh, my bad. I was applying it to Field2 after it was already like the OPs example. Yeah that CONVERT should work just fine.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-03-15 : 14:03:44

Thanks, Bustaz Kool and Lamprey. It works on SQL 2008 but does not work on SQL 2005. Do we have to change something on SQL 2005?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-15 : 14:10:08

In SQL 2005 I *think* you need a user defined function to make that conversion.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-03-15 : 15:20:29

No. You can use XML to do this.
See http://weblogs.sqlteam.com/peterl/archive/2010/01/27/Convert-binary-value-to-string-value.aspx
Replace SQL:VARIABLE with SQL:COLUMN like this

DECLARE @Table1 TABLE
	(
		Column1 VARBINARY(MAX)
	)

INSERT	@Table1
SELECT	0x41B207FE64F1643AD6E56A5DB2550D7E UNION ALL
SELECT	0xEAE6AFC34526384B7537421591170371 UNION ALL
SELECT	0xA550D55692A3FBB84775A024ADC5A837 UNION ALL
SELECT	0x573D2E533595DB65FE53063CC4FC7451 UNION ALL
SELECT	0xA1DDEE3A67C6DCC04C45D7E625BCAE5F

SELECT	Column1,
	CAST('' AS XML).value('xs:hexBinary(sql:column("Column1"))', 'VARCHAR(MAX)') AS ConvertedString
FROM	@Table1

N 56°04'39.26"
E 12°55'05.63"

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-03-15 : 15:40:44

Thanks guys xml conversion is perfect for me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources