How to perform bitwise operation on comma delimited column?

Question

I have a table which has 2 columns:

ID (INT) (PK)  NumList (VARCHAR)
1              2,4,25
2              2,12,25,33
3              3,10

How would I let's say add a column to this table and apply the OR (|) operator on the numbers in the list?

ID   NewCol
1    31
2    63
3    11

@RichardTheKiwi, bit wise or of the values in the numlist 3 | 10 = 11, quite mad really. — Tony Hopkinson, Oct 10 '12 at 20:56
If its splitting the multi value so you can operate on it, then have a look at this. http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco — Tony Hopkinson, Oct 10 '12 at 21:01
I ended up splitting the delimited numbers into different rows and then used a nested cursor to update the new column. I know loops and cursors are not efficient but since I have less than 50 rows, it is fine. — Ali_Abadani, Oct 10 '12 at 22:07

score 1 · Accepted Answer · answered Oct 11 '12 at 00:08

create table tbl (
    ID int,
    NumList VARCHAR(100));
insert tbl select
    1, '2,4,25' union all select
    2, '2,12,25,33' union all select
    3, '3,10';

select t.ID, t.NumList, sum(distinct power(2,v.number)) newcol
from tbl T
outer apply (values (cast('<a>'+REPLACE(NumList,',','</a><a>')+'</a>' as xml))) X(xml)
outer apply X.xml.nodes('/a') N(Num)
left join master..spt_values v on v.type='P' and v.number between 0 and 30
    and power(2,v.number) & N.Num.value('.','bigint') > 0
group by t.ID, t.NumList
order by t.ID

=== results
ID   NumList      newcol
---- ------------ -----------
1    2,4,25       31
2    2,12,25,33   63
3    3,10         11

score 0 · Answer 2 · answered Oct 10 '12 at 20:49

You would add the new column, and you'd probably have to write a trigger to do the calculations and populate the new column on insert or update whenever NumList is changed. The trigger would have to parse the numbers out of NumList, do the calculation then put the result in the new column.

See the link below for a description of the Bitwise operators in T-SQL (although it seems you already know how to use the operators, and were just wondering how to populate the column?)

http://msdn.microsoft.com/en-us/library/ms176122.aspx

How to perform bitwise operation on comma delimited column?

2 Answers2