我有存储在SQL一个int位掩码值。 我希望把这一数值为逗号分隔包含在位掩码值的值的列表。
因此,举例来说,结果看起来可能是这样:
id name bitMaskValue values
----------------------------------------
1 Bob 5 1,4
2 Mary 13 1,4,8
3 Stan 11 1,2,8
有没有一种方法在sql语句来做到这一点?
这是SQL Server 2008中。
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
这应该工作:
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
CTE + XPATH way
:
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
文章来源: In SQL, how can I split the values in a bitmask total into a comma delimited string