Prev: query help
Next: Semicolons
From: tress on 15 Jul 2010 06:37
Hi,

Wondering if anyone can help with a conversion type question, I have a table
in a SQL 2000 database which has a notes field with a field type of
Varbinary, the application that sits on top of this returns what the actual
string value of this field is, is there a way that I can recreate this using
TSQL.

Thanks PD
From: John Bell on 15 Jul 2010 17:55
On Thu, 15 Jul 2010 03:37:28 -0700, tress
<tress(a)discussions.microsoft.com> wrote:

>Hi,
>
>Wondering if anyone can help with a conversion type question, I have a table
>in a SQL 2000 database which has a notes field with a field type of
>Varbinary, the application that sits on top of this returns what the actual
>string value of this field is, is there a way that I can recreate this using
>TSQL.
>
>Thanks PD

Such as


CREATE TABLE binnotes ( id int not null identity,
binnote varbinary(20) not null )

INSERT INTO binnotes ( binnote )
SELECT CAST('Hello' AS varbinary(20))
UNION ALL SELECT CAST('World' AS varbinary(20))
UNION ALL SELECT CAST('Hello World' AS varbinary(20))

SELECT id, binnote, CAST(binnote as varchar(20)) AS ascnote
FROM binnotes

John
From: Erland Sommarskog on 15 Jul 2010 18:05
tress (tress(a)discussions.microsoft.com) writes:
> Wondering if anyone can help with a conversion type question, I have a
> table in a SQL 2000 database which has a notes field with a field type
> of Varbinary, the application that sits on top of this returns what the
> actual string value of this field is, is there a way that I can recreate
> this using TSQL.

convert(varchar(nn), bincol) or
convert(nvarchar(nn), bincol)

Assuming that it is as simple that the binary value is really a character
string, and anything more sophisticated. Which is not unlikely, since
else there would be little reason to use varbinary.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: tress on 22 Jul 2010 04:54
Hi both,

Thanks for your comments and yes Erland, there would be little point in
having that field type if the data was simple to get out with a convert
statement which it appears not to be. Well back to the drawing board and
thanks again for the replies.

Reg. Phil

"Erland Sommarskog" wrote:

> tress (tress(a)discussions.microsoft.com) writes:
> > Wondering if anyone can help with a conversion type question, I have a
> > table in a SQL 2000 database which has a notes field with a field type
> > of Varbinary, the application that sits on top of this returns what the
> > actual string value of this field is, is there a way that I can recreate
> > this using TSQL.
>
> convert(varchar(nn), bincol) or
> convert(nvarchar(nn), bincol)
>
> Assuming that it is as simple that the binary value is really a character
> string, and anything more sophisticated. Which is not unlikely, since
> else there would be little reason to use varbinary.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> .
>
From: John Bell on 22 Jul 2010 16:28
On Thu, 22 Jul 2010 01:54:54 -0700, tress
<tress(a)discussions.microsoft.com> wrote:

>Hi both,
>
>Thanks for your comments and yes Erland, there would be little point in
>having that field type if the data was simple to get out with a convert
>statement which it appears not to be. Well back to the drawing board and
>thanks again for the replies.
>
>Reg. Phil
>
>"Erland Sommarskog" wrote:
>
>> tress (tress(a)discussions.microsoft.com) writes:
>> > Wondering if anyone can help with a conversion type question, I have a
>> > table in a SQL 2000 database which has a notes field with a field type
>> > of Varbinary, the application that sits on top of this returns what the
>> > actual string value of this field is, is there a way that I can recreate
>> > this using TSQL.
>>
>> convert(varchar(nn), bincol) or
>> convert(nvarchar(nn), bincol)
>>
>> Assuming that it is as simple that the binary value is really a character
>> string, and anything more sophisticated. Which is not unlikely, since
>> else there would be little reason to use varbinary.
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>> .
>>

It looks like you need to check what populates the data. My guess Is
this data encrypted using some means.

John
 | 
Pages: 1
Prev: query help
Next: Semicolons