Bin to text - SQL Server Forums

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)

Bin to text

Author

Topic

dirtydavey
Yak Posting Veteran

80 Posts

Posted - 2007-05-01 : 05:35:36

Hi I have a binary(16) variable called @BinName it stores a vaule of :-

0xD11F59A19F9B944FBD59D6A534F0D067

I want to be a ble to see this in a varchar variable but with out trying to convert it to text. So for example, it should now be in a varchar(36) but still read :-

0xD11F59A19F9B944FBD59D6A534F0D067

Any idears? Its driving me nuts!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-01 : 16:49:01

[code]CREATE FUNCTION dbo.fnBIN2VC
(
@ts BINARY(16)
)
RETURNS VARCHAR(34)
AS
BEGIN
RETURN '0x'
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 1, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 1, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 2, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 2, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 3, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 3, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 4, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 4, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 5, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 5, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 6, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 6, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 7, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 7, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 8, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 8, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 9, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 9, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 10, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 10, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 11, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 11, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 12, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 12, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 13, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 13, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 14, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 14, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 15, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 15, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 16, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 16, 1) AS TINYINT) % 16, 1)
END[/code]
Peter Larsson
Helsingborg, Sweden

dirtydavey
Yak Posting Veteran

80 Posts

Posted - 2007-11-14 : 05:18:47

In the end I found this, works quite well:-

master.dbo.fn_varbintohexstr

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 05:36:52

Yes, if you have access to master database routines and cross your fingers that Microsoft do not remove the function in the future releases.

E 12°55'05.25"
N 56°04'39.16"

dirtydavey
Yak Posting Veteran

80 Posts

Posted - 2007-11-14 : 05:58:04

ha ha, yes thats allways a worrie. Your way is much better, but it worked for the time being!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 06:08:25

You mean you spent 6 months searching for a solution because you didn't like my suggestion?

E 12°55'05.25"
N 56°04'39.16"

dirtydavey
Yak Posting Veteran

80 Posts

Posted - 2007-11-14 : 06:18:31

he he, no not at all! LOL that would have been silly. I posted the question, then found my answer. And did not remember to post it. Then came back and found your answer, (thats much better!) and posted my answer too.

Thanks your your help though Peso, I will be using your way from now on, but mine does work for now.

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2007-11-14 : 07:55:31

LOL, I had the same problem 6 months ago en subcribed to this topic. But thx, both solutions work, and it happens that I just needed it!

Henri
~~~~
There's no place like 127.0.0.1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources