Binary Value Manipulation

I would like to drop the leading 0x on a binary value so I can do a
bitwise operation.
Here is simplified code:
select right(0x88186000,8)
I expected to get back 88186000, this was not the case. The command
returned some wierd characters.
Am I missing something?In this context 0x88186000 is a binary literal. There is no leading zero.
The "0x" is just part of the syntax for binary literals.
What bitwise operation are you trying to do? Lookup the bitwise operators in
Books Online. You probably won't get the answer you want by using string
operators such as RIGHT().
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 1 May 2007 12:01:27 -0700, tfeller wrote:
>I would like to drop the leading 0x on a binary value so I can do a
>bitwise operation.
(snip)
Hi tfeller,
I just replied to a copy of this message in comp.databases.ms-sqlserver.
In the future, please limit yoour questions to a single newsgroup - or
if you feel you really have to post to more than one group, use your
news program's crossposting function to post one copy to multiple groups
instead of posting independant copies. That prevents people from
spending time on a question that has already been answered in another
group.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Binary Value Manipulation

I would like to drop the leading 0x on a binary value so I can do a
bitwise operation.

Here is simplified code:

select right(0x88186000,8)

I expected to get back 88186000, this was not the case. The command
returned some wierd characters.

Am I missing something?tfeller <ToddFeller@.gmail.comwrote in news:1178044017.983887.201130
@.o5g2000hsb.googlegroups.com:

Quote:

Originally Posted by

I would like to drop the leading 0x on a binary value so I can do a
bitwise operation.
>
Here is simplified code:
>
select right(0x88186000,8)
>
I expected to get back 88186000, this was not the case. The command
returned some wierd characters.
>
Am I missing something?
>

What do you get back from SELECT 0x88186000 ?

I don't expect it will be 0x88186000

Try SELECT CAST(0x41424344 as varchar) and you should get back ABCD

The _representation_ (note emphasis) of a binary value _in T-SQL_ is a
sequence of characters that begin with 0x - but the 0x does not form part
of the _actual binary value_, any more than the starting and ending quotes
form part of a character value.|||The expressions in the bitwise operations are treated as binary numbers, and
one of the expressions can be a binary data type. So, you do not have to
convert your binary value, just use it directly. The results is of data type
integer and you can convert it back to binary. Try this:

SELECT CAST(0x88186000 ^ 2 AS binary(4)),
CAST(0x88186000 | 2 AS binary(4)),
CAST(0x88186000 & 2 AS binary(4))

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On 1 May 2007 11:26:58 -0700, tfeller wrote:

Quote:

Originally Posted by

>I would like to drop the leading 0x on a binary value so I can do a
>bitwise operation.
>
>Here is simplified code:
>
>select right(0x88186000,8)
>
>I expected to get back 88186000, this was not the case. The command
>returned some wierd characters.
>
>Am I missing something?

Hi tfeller,

Maybe the functions Peter DeBetta describes will help you to achieve
what you need:
http://sqlblog.com/blogs/peter_debe...-varbinary.aspx
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelissql

Binary Value Manipulation

I would like to drop the leading 0x on a binary value so I can do a
bitwise operation.
Here is simplified code:
select right(0x88186000,8)
I expected to get back 88186000, this was not the case. The command
returned some wierd characters.
Am I missing something?
On 1 May 2007 12:01:27 -0700, tfeller wrote:

>I would like to drop the leading 0x on a binary value so I can do a
>bitwise operation.
(snip)
Hi tfeller,
I just replied to a copy of this message in comp.databases.ms-sqlserver.
In the future, please limit yoour questions to a single newsgroup - or
if you feel you really have to post to more than one group, use your
news program's crossposting function to post one copy to multiple groups
instead of posting independant copies. That prevents people from
spending time on a question that has already been answered in another
group.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Binary Value Manipulation

I would like to drop the leading 0x on a binary value so I can do a
bitwise operation.
Here is simplified code:
select right(0x88186000,8)
I expected to get back 88186000, this was not the case. The command
returned some wierd characters.
Am I missing something?In this context 0x88186000 is a binary literal. There is no leading zero.
The "0x" is just part of the syntax for binary literals.
What bitwise operation are you trying to do? Lookup the bitwise operators in
Books Online. You probably won't get the answer you want by using string
operators such as RIGHT().
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 1 May 2007 12:01:27 -0700, tfeller wrote:

>I would like to drop the leading 0x on a binary value so I can do a
>bitwise operation.
(snip)
Hi tfeller,
I just replied to a copy of this message in comp.databases.ms-sqlserver.
In the future, please limit yoour questions to a single newsgroup - or
if you feel you really have to post to more than one group, use your
news program's crossposting function to post one copy to multiple groups
instead of posting independant copies. That prevents people from
spending time on a question that has already been answered in another
group.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Best Way To Aggregate a Large Table

I have a table that looks like this (simplified)
Account Period Amount
Assets 1 100
Assets 2 10
Assets 3 0
I want to create a new table to carry a balance for the amount column.
So that Period 1 = Period 1, Period 2 = Period 1 + Period 2, etc. Like
this:
Account Period Amount
Assets 1 100
Assets 2 110
Assets 3 110
The real table has 140 million rows and 25 columns. I can make this
work by doing 12 Insert statement in my new table but I'm thinking
there has to be a faster and more efficient way. TIA for any ideas.Try:
insert OtherTable
select
m.Account
, m.Period
, (select sum (i.Amount) from MyTable i
where i.Account = m.Account
and i.Period <= m.Period)
from
MyTable m
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Ctal" <witp_turns@.yahoo.com> wrote in message
news:1150927173.463636.315330@.c74g2000cwc.googlegroups.com...
I have a table that looks like this (simplified)
Account Period Amount
Assets 1 100
Assets 2 10
Assets 3 0
I want to create a new table to carry a balance for the amount column.
So that Period 1 = Period 1, Period 2 = Period 1 + Period 2, etc. Like
this:
Account Period Amount
Assets 1 100
Assets 2 110
Assets 3 110
The real table has 140 million rows and 25 columns. I can make this
work by doing 12 Insert statement in my new table but I'm thinking
there has to be a faster and more efficient way. TIA for any ideas.|||Not knowing the purpose, but it seems as though a VIEW may be a better
option. (Using Tom's query .) Storing calculated values seems a bit
'risky' -as well as unnecessary.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23N75rBYlGHA.3304@.TK2MSFTNGP03.phx.gbl...
> Try:
> insert OtherTable
> select
> m.Account
> , m.Period
> , (select sum (i.Amount) from MyTable i
> where i.Account = m.Account
> and i.Period <= m.Period)
> from
> MyTable m
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Ctal" <witp_turns@.yahoo.com> wrote in message
> news:1150927173.463636.315330@.c74g2000cwc.googlegroups.com...
> I have a table that looks like this (simplified)
> Account Period Amount
> Assets 1 100
> Assets 2 10
> Assets 3 0
> I want to create a new table to carry a balance for the amount column.
> So that Period 1 = Period 1, Period 2 = Period 1 + Period 2, etc. Like
> this:
> Account Period Amount
> Assets 1 100
> Assets 2 110
> Assets 3 110
>
> The real table has 140 million rows and 25 columns. I can make this
> work by doing 12 Insert statement in my new table but I'm thinking
> there has to be a faster and more efficient way. TIA for any ideas.
>|||Arnie Rowland wrote:
> Not knowing the purpose, but it seems as though a VIEW may be a better
> option. (Using Tom's query .) Storing calculated values seems a bit
> 'risky' -as well as unnecessary.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
I agree, but this is that unusual case where I need a real table
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23N75rBYlGHA.3304@.TK2MSFTNGP03.phx.gbl...
The query worked wonderfully, thanks Tom!