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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 convert binary to string...
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

naidu4u
Starting Member

USA
23 Posts

Posted - 06/27/2011 :  18:21:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3385 Posts

Posted - 06/28/2011 :  10:23:02  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3385 Posts

Posted - 06/28/2011 :  10:36:29  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

naidu4u
Starting Member

USA
23 Posts

Posted - 06/28/2011 :  11:40:33  Show Profile  Reply with Quote
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.)
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3385 Posts

Posted - 06/28/2011 :  12:09:37  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.

Edited by - nigelrivett on 06/28/2011 12:11:18
Go to Top of Page

naidu4u
Starting Member

USA
23 Posts

Posted - 06/28/2011 :  12:37:22  Show Profile  Reply with Quote
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....
Go to Top of Page

naidu4u
Starting Member

USA
23 Posts

Posted - 06/28/2011 :  12:43:26  Show Profile  Reply with Quote
the column format was binary(1056)...
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3385 Posts

Posted - 06/28/2011 :  13:00:35  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

naidu4u
Starting Member

USA
23 Posts

Posted - 06/28/2011 :  13:48:13  Show Profile  Reply with Quote
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...
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3385 Posts

Posted - 06/28/2011 :  14:51:24  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
???
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000