Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

binary to varchar

3 posts in Product Futures Discussion Last posting was on 2002-01-10 21:28:15.0Z
Niel_Juneja Posted on 2002-01-10 17:26:18.0Z
From: Niel_Juneja
Date: Thu, 10 Jan 2002 12:26:18 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: binary to varchar
Message-ID: <3E364D74BC4D0474005FCAE385256B3D.005FCAF685256B3D@webforums>
Lines: 39
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:951
Article PK: 95192

Hi,
Can anyone tell me as to how to convert a field which is stored in a
Sybase table as binary(24) to varchar(255). I want to get the hex
representation of the binary field into a string so that I can easily parse
it and then generate it's binary equivalent (i.e. a string of 0s and 1s).

when I retrieve a table field which is defined as binary(24), I am unable
to convert it to a string (I mean varchar).

For example:

when I run the following query (PaymentCaledars is defined as binary(24) ):

select PaymentCalendars from CashFlowCorpus where ....

it returns the following:

PaymentCalendars
--------------------------------------------------
0x000000000000000000000000000000000000000000000012

(1 row affected)

but if I do

select convert(varchar(255), PaymentCalendars) from CashFlowCorpus where
....

it returns a null string i.e. the following:

--------------------------------------------------

(1 row affected)



Any suggestions as to how to convert binary to varchar(255) ...

Many Thanks,
Niel.


Bret Halford Posted on 2002-01-10 18:34:52.0Z
Message-ID: <3C3DDECC.CF4AE89F@sybase.com>
Date: Thu, 10 Jan 2002 11:34:52 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: binary to varchar
References: <3E364D74BC4D0474005FCAE385256B3D.005FCAF685256B3D@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 42
NNTP-Posting-Host: 157.133.80.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:950
Article PK: 95187


Niel_Juneja wrote:

> Hi,
> Can anyone tell me as to how to convert a field which is stored in a
> Sybase table as binary(24) to varchar(255). I want to get the hex
> representation of the binary field into a string so that I can easily parse
> it and then generate it's binary equivalent (i.e. a string of 0s and 1s).
>
> when I retrieve a table field which is defined as binary(24), I am unable
> to convert it to a string (I mean varchar).
>
> For example:
>
> when I run the following query (PaymentCaledars is defined as binary(24) ):
>
> select PaymentCalendars from CashFlowCorpus where ....
>
> it returns the following:
>
> PaymentCalendars
> --------------------------------------------------
> 0x000000000000000000000000000000000000000000000012
>
> (1 row affected)
>
> but if I do
>
> select convert(varchar(255), PaymentCalendars) from CashFlowCorpus where
> ....
>
> it returns a null string i.e. the following:
>
> --------------------------------------------------
>
> (1 row affected)
>
> Any suggestions as to how to convert binary to varchar(255) ...
>
> Many Thanks,
> Niel.

Yes. The INTTOHEX() function will do this for you 4 bytes at a time, it is
just
a matter of concatenating the results together:

create table mytable (x binary(24))
go
insert mytable values (0x1234567890abcdef1234567890abcdef)
go
select inttohex(convert(int,substring(x,1,4)))
+ inttohex(convert(int,substring(x,5,8)))
+ inttohex(convert(int,substring(x,9,12)))
-- [ etc.]
from mytable
go
------------------------
1234567890ABCDEF12345678

(1 row affected)


njuneja Posted on 2002-01-10 21:28:15.0Z
From: njuneja
Date: Thu, 10 Jan 2002 16:28:15 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: binary to varchar
Message-ID: <79B994BC499BF4630075F16D85256B3D.0069619685256B3D@webforums>
References: <3E364D74BC4D0474005FCAE385256B3D.005FCAF685256B3D@webforums> <3C3DDECC.CF4AE89F@sybase.com>
Lines: 1
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:949
Article PK: 95190

thanks very much Bret. works great.