Concatenating string and HEX value into Binary field

[stable]

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

 

[SimonSellick]

Hi,

I'm unsure when you specify 'binary(20)' what actually gets stored - is it 20 bytes containing any bit pattern you like? If so, then that is the same as a fixed-length CHAR(20) field, so you could test assigning that from your SQL database to a record in your target, to make sure that it accepts all values. Once happy with that, you just need to fill your SQL CHAR(20) field, which you can do (most elegantly in a function) with judicious use of chr(), ascii(), convert() and perhaps some lookup code.

Have you any info about what you want to put into the concatenated field?

Simon.

 

[stable]

Hi Simon,

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.

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