How do I get specific data from the table

Hi All,
I am using Microsoft SQL Server 2014.
I need data say only for sector 7 i.e. 'Test7' but I am getting data for 'Test6' also.

create table test_data1(Name varchar(100),sector varchar(100))

insert into test_data1 values('Test1','22,23,24')
insert into test_data1 values('Test2','2')
insert into test_data1 values('Test3','2')
insert into test_data1 values('Test4','2,3,4,9')
insert into test_data1 values('Test5','26')
insert into test_data1 values('Test6','13,14,27')
insert into test_data1 values('Test7','5,6,7')

What I have tried:

select * from test_data1

Name	sector
Test1	22,23,24
Test2	2
Test3	2
Test4	2,3,4,9
Test5	26
Test6	13,14,27
Test7	5,6,7

select * from test_data1 where sector like '%7' or sector like '%,7' or sector like '7,%' or sector like '%,7,%'

Name	sector
Test6	13,14,27
Test7	5,6,7

Although this query is working properly for sector 2 and sector 22 where all data related to sector 2 and 22 is getting displayed properly.


select * from test_data1 where sector like '%2' or sector like '%,2' or sector like '2,%' or sector like '%,2,%'

Name	sector
Test2	2
Test3	2
Test4	2,3,4,9

select * from test_data1 where sector like '%22' or sector like '%,22' or sector like '22,%' or sector like '%,22,%'

Name	sector
Test1	22,23,24

Can anybody please help me to get data only for sector 7 i.e. data in the following manner?

Name	sector
Test7	5,6,7

Tags: SQL, Server