Concatenating String and HEX value into Binary field

Question

Concatenating String and HEX value into Binary field

Stable

SSC Veteran

Points: 268
More actions
February 26, 2009 at 6:31 pm

#200299

Hi there,
I'm new to Transact SQL and working with a legacy system where the key to a table is a binary(20) field. The source code for this legacy system is not available.
We would like to add new records to this table using data from another system by running a daily transact SQL script. The problem is that the key appears to be comprised of several fields with different datatypes.
Analysing an existing record key, I deduced the following:
Column Name Data Type Value
UniqueID Char(12) '000000000014'
Group Char(1) 'C'
Code Char(13) '100099-50 CAd'
The HEX value of '100099-50 CAd' looks like this:
3130303039392d353020434164
The binary(20) key representation looks like this:
0x0000000000014C3130303039392D353020434164
Is it possible to concatenate a string value and a HEX value and then output to a binary value in this format? And how would I go about it?
Any help would be much appreciated.
Cheers
Stable

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 1

[font="Verdana"]I suggest you have a look at SQL Server Books Online (the online help) at the binary data type, and also at user defined functions. It shouldn't be too hard to build some functions that convert back and forward between the formats you want.[/font]

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 2

I'd bet credits to Navy beans that a legacy system also has legacy code. Search sys.SQL_Modules.Definition for the column name using LIKE '%columnamehere%' and then use the OBJECT_NAME(OBJECT_ID) on the results to get the name of the proc, function, or trigger that does this in the legacy code.

When you find it, it would be really cool if you'd post it so we can see it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stable SSC Veteran Points: 268 More actions · Answer 3

Thanks Bruce and Jeff.

Sorry for the delay in responding to your reply.

In SQL Management Studio when I run Transac SQL the Binary(20) field looks like this:

0x0000000000014C3130303039392D353020434164

We have noticed that the legacy system does not accept certain fields updated via Transac SQL , even Character datatype fields.

I searched sys.SQL_Modules.Definition for the column name and did not find anything. There are no procedures, function, or trigger for this database

As we do not have access to the code for the legacy system the compromise is to update minimum number of fields on current records and this appears to work OK.

Thanks for your help.

Stable

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 4

The method I've used to construct the binary(20) key is as follows:

@UniqueID and @Group are apparently both hexadecimal strings. These are concatenated together and the concatenated hexadecimal string is then converted to a numeric bigint value (using a Tally table to do the hexadecimal to decimal conversion). This numeric value is then converted to binary(7) to which is appended the @Code char(13) value converted to binary(13).

DECLARE @UniqueID Char(12)

SET @UniqueID = '000000000014'

DECLARE @Group Char(1)

SET @Group = 'C'

DECLARE @Code Char(13)

SET @Code = '100099-50 CAd'

/* Concatenate @UniqueID and @Group */

DECLARE @Hex char(13)

SET @Hex = @UniqueID + @Group

/* Convert @Hex string to a bigint */

DECLARE @Value bigint

SELECT @Value = SUM(POWER(CAST(16 AS bigint), T.N)

* (CHARINDEX(SUBSTRING(@Hex, 13 - T.N, 1), '0123456789ABCEDF') - 1))

FROM Tally T

WHERE (T.N >= 0 AND T.N < 13)

/* Convert @Value and @Code to binary data types and concatenate */

SELECT CONVERT(binary(7), @Value) + CONVERT(binary(13), @Code)

This procedure gives your expected result.

0x0000000000014C3130303039392D353020434164

Stable SSC Veteran Points: 268 More actions · Answer 5

Hi Andrew,

Thanks very much for your detailed explanation on how to convert the Hex string into a binary field. I've not seen the use of the Tally table before and found it really interesting the way you went about this.

I have noticed that the last character in @Hex (@Group value of 'C' ) is converted to a 0 and I get the end result of:

0x000000000001403130303039392D353020434164 not

0x0000000000014C3130303039392D353020434164

Any ideas on how to fix this?

Cheers

Stable

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 6

I have noticed that the last character in @Hex (@Group value of 'C' ) is converted to a 0 and I get the end result of:
0x000000000001403130303039392D353020434164 not
0x0000000000014C3130303039392D353020434164
Any ideas on how to fix this?

The most likely reason is that your Tally table is missing the number 0.

Alternatively, use the following that uses the numbers 1 - 13 (rather than 0 - 12) from the Tally table.

SELECT @Value = SUM(POWER(CAST(16 AS bigint), T.N - 1)

* (CHARINDEX(SUBSTRING(@Hex, 14 - T.N, 1), '0123456789ABCEDF') - 1))

FROM Tally T

WHERE (T.N >= 1 AND T.N <= 13)

Stable SSC Veteran Points: 268 More actions · Answer 7

Hi Andrew,

Thanks so much for your quick reply.

Yes my Tally table is missing the number 0. I have used your suggested SQL statemensts and it works fine now.

Thanks again,

Stable