quote:

---

Originally posted by jezemine

-- a unicode string
declare @string nvarchar(100)
set @string='hello world'

-- convert it to hex
declare @hex varbinary(100)
set @hex=convert(varbinary(100), @string)

-- convert it back to unicode
select @string, @hex, convert(nvarchar(100), @hex)

---

elsasoft.org

---

quote:

---

Originally posted by jezemine
declare @hexstring varchar(max);
set @hexstring = '0x5069CC847069CC8420617261207061CC846E75692E2054616B7520776861CC846E6175202F206E61CC8420486FCC846E65204170616E7569203B206E61CC842053636F74742050656172736F6E206E6761CC842070696B697469612E20';
declare @hex varbinary(max)
select @hex=cast('' as xml).value('xs:hexBinary( substring(sql:variable("@hexstring"), sql:column("t.pos")) )', 'varbinary(max)')
from (select case substring(@hexstring, 1, 2) when '0x' then 3 else 0 end) as t(pos)
select convert(nvarchar(100), @hex)

---

quote:

---

Originally posted by Arnold Fribble

quote:

---

Originally posted by jezemine
declare @hexstring varchar(max);
set @hexstring = '0x5069CC847069CC8420617261207061CC846E75692E2054616B7520776861CC846E6175202F206E61CC8420486FCC846E65204170616E7569203B206E61CC842053636F74742050656172736F6E206E6761CC842070696B697469612E20';
declare @hex varbinary(max)
select @hex=cast('' as xml).value('xs:hexBinary( substring(sql:variable("@hexstring"), sql:column("t.pos")) )', 'varbinary(max)')
from (select case substring(@hexstring, 1, 2) when '0x' then 3 else 0 end) as t(pos)
select convert(nvarchar(100), @hex)

---

Ah, I think you'll get more sense if you use varchar(200) than nvarchar(100) in that final convert: the string is UTF-8, not UTF-16.
(And despite what you get with the nvarchar(100) version, it looks like Maori, not Chinese to me!)

It says: "Pi¯pi¯ ara pa¯nui. Taku wha¯nau / na¯ Ho¯ne Apanui ; na¯ Scott Pearson nga¯ pikitia."

Sadly, all the combining macrons get turned into spacing ones when I post that!

But using "select convert(varchar(200), @hex)" isn't really the right answer: you really want to convert the UTF-8 octets into nvarchars.

In this case you could use "SELECT CAST(CAST(@hex AS xml) AS nvarchar(max))" as the last line, but this isn't generally a good idea since the five characters &<>"' will parsed by the XML parser!

---