--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([VALUE] int,[DESCRIPTION] varchar(30))
insert [tbl]
select 1,'相当于shift 1 Enabled' union all
select 2,'相当于Shift 2 Enabled' union all
select 3,'相当于Shift 1&2 Enabled' union all
select 4,'相当于Shift 3 Enabled]' union all
select 5,'相当于Shift 1&3 Enabled]' union all
select 6,'相当于Shift 2&3 Enabled]' union all
select 7,'相当于Shift 1,2&3 Enabled'
--> 测试数据：[shift]
if object_id('[shift]') is not null drop table [shift]
create table [shift]([id] int,[shift_nbr] int)
insert [shift]
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,1 union all
select 5,2 union all
select 6,3

select * from [shift] 
where CHARINDEX(ltrim([shift_nbr]),(select [DESCRIPTION] from tbl where [VALUE]=4))>0
/*
id    shift_nbr
3    3
6    3
*/
select * from [shift] 
where CHARINDEX(ltrim([shift_nbr]),(select [DESCRIPTION] from tbl where [VALUE]=6))>0
/*
id    shift_nbr
2    2
3    3
5    2
6    3
*/
select * from [shift] 
where CHARINDEX(ltrim([shift_nbr]),(select [DESCRIPTION] from tbl where [VALUE]=7))>0
/*
id    shift_nbr
1    1
2    2
3    3
4    1
5    2
6    3
*/

------解决方案--------------------

不是相当于做‘位’的操作，就是位操作。

SQL code
--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int, shift_nbr int)
insert into #
select 1, 1 union all
select 2, 2 union all
select 3, 3 union all
select 4, 1 union all
select 5, 2 union all
select 6, 3

declare @shift_cntrl_nbr int = 7
select * from # where @shift_cntrl_nbr & power(2, shift_nbr-1)>0

/*
id          shift_nbr
----------- -----------
1           1
2           2
3           3
4           1
5           2
6           3
*/