hello everyone,

this was so oddball, i didn't even try looking for it in the groups,
because i wouldn't know what to search for. here's what i'm trying to
do (abstractly - this is not a real table, what i'm curiosu about is
the update statement):

create table intvalues ( int value not null )
go

insert into intvalues values(123)
insert into intvalues values(432)
insert into intvalues values(12)
insert into intvalues values(4321)
insert into intvalues values(1234)
insert into intvalues values(564)
go

what i'm curious about is how to change each int value into the highest
power of two value included in the number. for example, after the
update the values inserted above should read:

select * from intvalues
go

64
256
8
4096
1024
512

i'm guessing this update statement can be performed with modulus and
bit shifting, but i'm at a loss for how to do it. thanks for any help!

jason

err, make that:

create table intvalues ( value int not null)
go

obviously :)

actually, never mind, i found a decent solution. i know how high the
int values can possibly go, so i'll just do this:

update intvalues set value = 4096 where value & 4096 = 4096
go
update intvalues set value = 2048 where value & 2048 = 2048
go

working down from the highest known values, this should work just fine.

Jason,

Another solution is this:

cast(power(2,floor(log(value)/log(2))) as int)

I can't guarantee that there the floating-point arithmetic used
by the log function and division won't cause this to be wrong
for some values, but I think the ~16 decimal digit precision of
float will suffice to assure correct answers for all integers.

It will not generalize to bigint, however, since bigint values
have precision that exceeds the precision of float.   The output
of the repro below gives the wrong answer for the largest value.

create table intvalues ( value bigint not null )
go

insert into intvalues values(123)
insert into intvalues values(432)
insert into intvalues values(12)
insert into intvalues values(4321)
insert into intvalues values(1234)
insert into intvalues values(564)
insert into intvalues values(288230376151711300)
go

select
  value, cast(power(cast(2 as bigint),floor(log(value)/log(2))) as
bigint) as pow2
from intvalues
go

-- drop table intvalues


You could also compare the values with those from a permanent,
derived, or TVF table of powers of two.  If that table is indexed,
the solution will be reasonable efficient, though not as efficient as
the solution above, but it will work for all bigint values.

Here's a solution that works in SQL Server 2005, using CTEs for fun:

create function powers (
) returns @t table (
  p bigint primary key
)
as begin

declare @two bigint set @two = 2;
with Four(d) as (
  select 0 union all select 1 union all select 2 union all select 3
), N(n) as (
  select a.d*16+b.d*4+c.d as n from
  Four as a, Four as b, Four as c
), P(p) as (
  select power(@two, n) from N
  where n < 63
)
insert into @t select p from P
return
end
go

select
  value, max(p) as pow2
from intvalues
cross join powers()
where p <= value
group by value
go

Steve Kass
Drew University

jason wrote:

Show quoteHide quote

another set-based approach

drop table #ntvalues
go
create table #ntvalues ( value bigint not null )
go


insert into #ntvalues values(123)
insert into #ntvalues values(432)
insert into #ntvalues values(12)
insert into #ntvalues values(4321)
insert into #ntvalues values(1234)
insert into #ntvalues values(564)
go
select value, mp2, value-mp2 diff
from (select value

,(select max(p2) from (
select power(2,i) p2
--into #power2
from (
select 0 i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
union all
select 13
) t
)p2
where p2.p2&value > 0) mp2
from #ntvalues ) T

value                mp2         diff
-------------------- ----------- --------------------
123                  64          59
432                  256         176
12                   8           4
4321                 4096        225
1234                 1024        210
564                  512         52

holy crap :) thanks, i'll have to digest this one for while before i
grasp it

thanks! even though i had implemented the solution, i love learning a
thing or two from the other hardcore enthusiasts :D

Show quote Hide quote Just for fun using UDF.
Handles BIGINT and negative numbers.
Returns 1 or -1 if no power found, that is, @num = 1, 0, -1
Returns the power > @num if @num is negative.

CREATE FUNCTION highPower2 (@num BIGINT)
RETURNS BIGINT
AS
BEGIN

DECLARE @result BIGINT

SET @result = CASE WHEN @num < 0 THEN -1 else 1 END

WHILE @num <> 0
BEGIN
  SET @num = @num / 2

  IF @num <> 0
   SET @result = @result * 2
END

RETURN (@result)
END

SELECT DBO.highPower2(123)

i've saved this for later review! thanks a lot :)

XOR and foreign key constraints
Like operator returns unexpected records
drop users connected to a database
default clustered index when selecting primary key
Variables Question - Wish to hide results.