Menu
I have a SQL Server 2008 R2 table with nvarchar(4000) field.
Data that stores this table look like
'696D616765206D61726B65643A5472'
or
'303131' ("011").
I see that each char is encoding to hex.
How can I read those data from table? I don't want write decoding function, I mean that simpler way exists.
P.S. Sorry for my English.
796
Use Virtual View to load the data to Data Vault, and in Virtual View, use the CONVERT() function of SQL Server to extract NVARCHAR(MAX) data as VARCHAR.
For NCHAR / NVARCHAR , the character encoding is UCS-2 or UTF-16, depending if the database collation is using the SC option. The length N in N[VAR]CHAR(N) defines a number of byte-pairs, not bytes. For UCS-2 this corresponds to a number of characters.
Unicode Data Types. Data types nchar, nvarchar, and long nvarchar are used to store Unicode data. They behave similarly to char, varchar, and long varchar character types respectively, except that each character in a Unicode type typically uses 16 bits.
The NVARCHAR data type stores strings of varying lengths. The string can include digits, symbols, and both single-byte and (in some locales) multibyte characters. The main difference between VARCHAR and NVARCHAR data types is the collation order.
SQL Server 2008 actually has a built-in hex-encoding and decoding feature!
Sample (note the third parameter with value "1" when converting your string to VarBinary):
DECLARE @ProblemString VarChar(4000) = '54657374'
SELECT Convert(VarChar, Convert(VarBinary, '0x' + @ProblemString, 1))
Ref: http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx
The advantage of this approach is that you don't need the "Exec" call, which you generally try to avoid, for fear of injection among other things. The disadvantage is that it only works in SQL Server 2008 and later.
170
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
