I have a bitmask value stored as an int in sql. I'd like to turn that value into a comma separated list of the values contained in the bitmask value.
我有一个位掩码值存储为sql中的int。我想将该值转换为逗号分隔的位掩码值中包含的值列表。
So, for example, the results might look like so:
因此,例如,结果可能如下所示:
id name bitMaskValue values
----------------------------------------
1 Bob 5 1,4
2 Mary 13 1,4,8
3 Stan 11 1,2,8
Is there a way to accomplish this in a sql statement?
有没有办法在sql语句中完成此操作?
This is SQL Server 2008.
这是SQL Server 2008。
3 个解决方案
#1
1
declare @I integer = 2117
Declare @v varChar(32) = ''
Declare @Bit tinyInt = 0
while @I > 0 Begin
Set @v += case @I %2 WHen 1 Then str(@bit,2,1) + ',' else '' End
Set @Bit += 1
Set @i /= 2
End
Select case When len(@v) > 0 Then left(@v, len(@v) -1) else '' End
#2
5
This should work:
这应该工作:
SELECT id, name, bitMaskValue,
SUBSTRING(
CASE WHEN bitMaskValue & 1 = 1 THEN ',1' ELSE '' END
+ CASE WHEN bitMaskValue & 2 = 2 THEN ',2' ELSE '' END
+ CASE WHEN bitMaskValue & 4 = 4 THEN ',4' ELSE '' END
+ CASE WHEN bitMaskValue & 8 = 8 THEN ',8' ELSE '' END
, 2, 64) As [values]
FROM yourTable
#3
1
CTE + XPATH way
:
CTE + XPATH方式:
set nocount on
declare @t as table(id int, name varchar(100), bitMaskValue int)
insert into @t(id, name, bitMaskValue) values(1,'Bob',5)
insert into @t(id, name, bitMaskValue) values(2,'Mary',13)
insert into @t(id, name, bitMaskValue) values(3,'Stan',11)
;with cte(num) as
(
select 1
union all
select num*2
from cte
)
select
id,
name,
bitMaskValue,
stuff((
select cast((
select isBitSet
from
(
select top 31 num,
case
when bitMaskValue & num = num then ',' + cast(num as varchar(10))
else null
end isBitSet
from cte
) tmp
where isBitSet is not null
for xml path('')) as xml).value('.', 'VARCHAR(max)')
), 1, 1, '') bitSet
from @t
#1
1
declare @I integer = 2117
Declare @v varChar(32) = ''
Declare @Bit tinyInt = 0
while @I > 0 Begin
Set @v += case @I %2 WHen 1 Then str(@bit,2,1) + ',' else '' End
Set @Bit += 1
Set @i /= 2
End
Select case When len(@v) > 0 Then left(@v, len(@v) -1) else '' End
#2
5
This should work:
这应该工作:
SELECT id, name, bitMaskValue,
SUBSTRING(
CASE WHEN bitMaskValue & 1 = 1 THEN ',1' ELSE '' END
+ CASE WHEN bitMaskValue & 2 = 2 THEN ',2' ELSE '' END
+ CASE WHEN bitMaskValue & 4 = 4 THEN ',4' ELSE '' END
+ CASE WHEN bitMaskValue & 8 = 8 THEN ',8' ELSE '' END
, 2, 64) As [values]
FROM yourTable
#3
1
CTE + XPATH way
:
CTE + XPATH方式:
set nocount on
declare @t as table(id int, name varchar(100), bitMaskValue int)
insert into @t(id, name, bitMaskValue) values(1,'Bob',5)
insert into @t(id, name, bitMaskValue) values(2,'Mary',13)
insert into @t(id, name, bitMaskValue) values(3,'Stan',11)
;with cte(num) as
(
select 1
union all
select num*2
from cte
)
select
id,
name,
bitMaskValue,
stuff((
select cast((
select isBitSet
from
(
select top 31 num,
case
when bitMaskValue & num = num then ',' + cast(num as varchar(10))
else null
end isBitSet
from cte
) tmp
where isBitSet is not null
for xml path('')) as xml).value('.', 'VARCHAR(max)')
), 1, 1, '') bitSet
from @t