Concatenating String and HEX value into Binary fie

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Concatenating String and HEX value into Binary fie

Author

Topic

Stable
Starting Member

2 Posts

Posted - 2009-02-26 : 20:42:27

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

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-26 : 20:53:33

Try this

SELECT KEY = CAST(UniqueId + Group + Code AS BINARY(20)) from yourtable.

Stable
Starting Member

2 Posts

Posted - 2009-02-26 : 21:13:55

Thanks vijayisonly.

Unfortunately that does not give me the value:

0x0000000000014C3130303039392D353020434164

which looks like the concatenation of a char(13) and and the HEX value of a Char(13) stored in a binary (20) field.

Cheers
Stable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources