convert binary to string... - 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 2000 Forums

SQL Server Development (2000)

convert binary to string...

Author

Topic

naidu4u
Starting Member

23 Posts

Posted - 2011-06-27 : 18:21:38

Hello All,

I was actually trying to convert binary to string format...

I've implemented some statements like

cast( column_name as nvarchar),
convert(varchar, column_name, 0) or convert(varchar, column_name, 1) or convert(varchar, column_name, 2) which obviously didn't work...

My column has the data in the binary format which is starting with 0x

Appreciate any help on this....

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-28 : 10:23:02

What string representation do you want?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-28 : 10:36:29

maybe
declare @b binary(8) = 0x1234567890abcdef
;with cte as
(
select i = 1
union all
select i = i + 1 from cte where i < LEN(@b)
) ,
cte2 as
(
select i, j=convert(int,SUBSTRING(@b,i,1)) from cte
),
cte3 as
(
select i, j=j/16, k=j%16 from cte2
),
ctehex as
(
select i, j, k, h1=case when j<10 then convert(varchar(1),j) else CHAR(55+j) end
, h2=case when k<10 then convert(varchar(1),k) else CHAR(55+k) end
from cte3
)
select h = '0x'+(select h1+h2 from ctehex for xml path (''))

result
'0x1234567890ABCDEF'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

naidu4u
Starting Member

23 Posts

Posted - 2011-06-28 : 11:40:33

Thanks for the reply nigelrivett.

But I can just query the database...I have to write a query (for example: select x1, x2, x3 from table in which x2 is a binary column and I need to convert that to a string or varchar format. So that, I can write my outer queries.)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-28 : 12:09:37

You still don't want to say what format you want?
Maybe

declare @t table (id int identity, b binary(8))
insert @t(b) values(0x1234567890abcdef), (0xabcdef0123456789)

;with ctea as
(
select maxlen = MAX(len(b)) from @t
) ,
cte as
(
select i = 1
union all
select i = i + 1 from cte,ctea where i < maxlen
) ,
cte2 as
(
select id, i, j=convert(int,SUBSTRING(b,i,1)) from cte, @t
),
cte3 as
(
select id, i, j=j/16, k=j%16 from cte2
),
ctehex as
(
select id, i, j, k, h1=case when j<10 then convert(varchar(1),j) else CHAR(55+j) end
, h2=case when k<10 then convert(varchar(1),k) else CHAR(55+k) end
from cte3
)
select t.id, h = '0x'+(select h1+h2 from ctehex where ctehex.id = t.id for xml path (''))
from @t t

result
id h
----------- ---------------------
1 0x1234567890ABCDEF
2 0xABCDEF0123456789

Just noticed you are v2000
Can't use a cte for that - you can probably do something similar to the above using temp tables or a recusive function.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

naidu4u
Starting Member

23 Posts

Posted - 2011-06-28 : 12:37:22

Cant we implement this by simple SQL and some functions...since I'm a cognos developer, am not familiar with the code you wrote...Sorry I'm using SQL 2000. As far as I understood this is some kind of a precedure...can we convert the column without a procedure...?

Thanks a lot for the reply....

naidu4u
Starting Member

23 Posts

Posted - 2011-06-28 : 12:43:26

the column format was binary(1056)...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-28 : 13:00:35

try
CONVERT(varchar(100),fld,1)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

naidu4u
Starting Member

23 Posts

Posted - 2011-06-28 : 13:48:13

quote:
Originally posted by nigelrivett

try
CONVERT(varchar(100),fld,1)

I've tried using the cast and convert as mentioned in the msdn that they are implicit conversions...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-28 : 14:51:24

???
A convert is an implicit convertion? Wonder what an explicit convertion is.

Why do you care about what they are called anway.
Not convinced this is aa serious thread.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources