Greetings all. I'm using SQL Server 2008 R2 Express, and I've got a linked server to a DB2 mainframe. There is one particular field where I must use the DB2 HEX function to return a varchar value. I have been using OPENQUERY, so have been able to pull out what I need with the DB2 HEX function. I'll give one example to illustrate. If I do this...
HEX(Column1) AS HEX_VAL
WHERE ID = 1
My results look like this
ID Column1 HEX_VAL LENGTH
1 B C200000012700CD400000003673CD500000000850C 42
Now, I know that the HEX_VAL string is simply a string of 14 character pieces (I'll call them "words"), from which I can extract a value by pulling out substrings. For example, CAST(SUBSTRING(HEX_VAL,3,11) AS DECIMAL(11,2))/100.0 = 127.00. No matter how many "words" are in the string, I can use a Tally table to reliably pull out what I need. My problem is that I have figured out how to configure my linked server so that I can use 4 part naming convention instead of OPENQUERY. This is great, however, there is no SQL Server equivalent to what the DB2 HEX function is doing. The closest I can muster is to convert to varbinary, which looks like this...
SELECT TOP 1
VARBIN = CAST(Column1 AS VARBINARY),
LENGTH = LEN(Column1)
WHERE ID = 1
ID Column1 VARBIN LENGTH
1 B 0x4200000012F80C4D00000003C5144E00000000650C 42
So, I guess I have a couple of questions.
1) Why does the value of the field return 'B' when no conversion is happening, even though SQL Server knows the len is 42?
2) What is the difference between ...
C200000012700CD400000003673CD500000000850C and 0x4200000012F80C4D00000003C5144E00000000650C?
3) Is there a way to turn the second string into the first string?
4) Knowing that I can pull out all the values I need from the HEX string by doing...
DECLARE @string VARCHAR(100)
SELECT @string = 'C200000012700CD400000003673CD500000000850C'
SUBSTRING(@string,CASE WHEN N = 3 THEN N ELSE N + 3 END,11)
FROM tally t
WHERE (t.n = 3
OR t.N%14 = 0)
AND t.N < LEN(@string)
... is there a way to pull out the same values from the VARBIN string? Other than the length of the two strings, I don't see any reliable patterns. I sure would appreciate any insight into any aspect of my dilema, and, as always, I very much appreciate the time you all take to consider other's problems.
The glass is at one half capacity: nothing more, nothing less.