April 7, 2011 at 11:36 am
We have a table with single column where store integer value.
Like this:
Declare @t table (v integer not null primary key, check(v >= 0));
Insert @t values (0),(1),....(6),(7)
I need following result based on @t table:
valued-----bit_4--------bit_2------bit_1
===================================
0------------0----------0-----------0
1------------0----------0-----------1
...
7------------1----------1------------1
How you querying this to show bit pattern in individual columns?
April 7, 2011 at 11:51 am
SELECT
v,
v&1 AS bit1,
v&2 / 2 AS bit2,
v&4 / 4 AS bit4,
...
FROM
table
You're looking for the bitwise operators, & and | . Divide by the bit value afterwards to turn it into a 0/1.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 7, 2011 at 11:53 am
Or... (instead of divide)
Declare @t table (v integer not null primary key, check(v >= 0));
Insert @t values (0)
Insert @t values (1)
Insert @t values (2)
Insert @t values (3)
Insert @t values (4)
Insert @t values (5)
Insert @t values (6)
Insert @t values (7)
SELECT
v
, CONVERT(BIT, v & 1) AS V1
, CONVERT(BIT, v & 2) AS V2
, CONVERT(BIT, v & 3) AS V3
, CONVERT(BIT, v & 4) AS V4
, CONVERT(BIT, v & 5) AS V5
, CONVERT(BIT, v & 6) AS V6
, CONVERT(BIT, v & 7) AS V7
FROM
@t
April 7, 2011 at 12:02 pm
Thank you,
Please compare the results.
Declare @t table (v integer not null primary key, check(v >= 0));
Insert @t values (0)
Insert @t values (1)
Insert @t values (2)
Insert @t values (3)
Insert @t values (4)
Insert @t values (5)
Insert @t values (6)
Insert @t values (7)
SELECT
v
, CONVERT(BIT, v & 1) AS V1
, CONVERT(BIT, v & 2) AS V3
, CONVERT(BIT, v & 4) AS V4
FROM
@t
/* My wanted result by Ninja's_RGR'us
v V1 V3 V4
----------- ----- ----- -----
0 0 0 0
1 1 0 0
2 0 1 0
3 1 1 0
4 0 0 1
5 1 0 1
6 0 1 1
7 1 1 1
*/
SELECT
v,
v&1 AS v1,
v&2 / 2 AS v2,
v&4 / 4 AS v4
FROM
@t
/* Incorrect Reslut by Craig Farrell
v v1 v2 v4
----------- ----------- ----------- -----------
0 0 0 0
1 1 1 1
2 0 0 0
3 1 1 1
4 0 0 0
5 1 1 1
6 0 0 0
7 1 1 1
*/
April 7, 2011 at 12:07 pm
Hm, odd. Order of operations is division then bitwise check? Thought bitwise took precedence. Ah well, wrap it in parens then. 😉
At least you checked to confirm. 😎
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 8, 2011 at 2:04 am
CELKO (4/7/2011)
Yes, you can kludge it, but this is not how SQL is meant to be used. It is a high level, abstract data base language and not assembly language. All you are doing in preventing portable code and any hope of optimization.
So please tell us how it should be done - I for one would be interested to know
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply