sql - how to search and retrieve the matching word from a comma separated string in one column -

i have problem bus table contain:

bus_no(int) bus_route(varchar) bus_stop(varchar) 

my bus_stop column contains stop comma separated string given below:

'apsara,shahadara,shakarpur,mother dairy,badarpur,' 

i need search particular word shakarpur without comma in sql server.

if want match check , select const:

select busno,         busroute,         case when busstop = 'shakarpur' or                   busstop 'shakarpur,%' or                  busstop '%,shakarpur,%' or                  busstop '%,shakarpur'             'shakarpur' end busstop bus 

if have parameter then:

select busno,         busroute,         case when busstop = @p or                   busstop @p + ',%' or                  busstop '%,' + @p + ',%' or                  busstop '%,' + @p             @p end busstop bus 

edit:

here solution part matching:

declare @t table     (       bus_no int ,       bus_route varchar(20) ,       bus_stop varchar(200)     ) insert  @t values  ( 1, '1', 'apsara,shahadara,shakarpur,mother dairy,badarpur' ),         ( 2, '2', 'amsterdam,brussels,bruges,brussels,london,liverpool' ),         ( 3, '3', 'k,k,k' );   ;with cte as(select  bus_no ,                      bus_route ,                      split.a.value('.', 'varchar(100)') bus_stop    ( select    bus_no ,                     bus_route ,                     cast ('<m>' + replace(bus_stop, ',', '</m><m>') + '</m>' xml) data                @t         ) t         cross apply data.nodes('/m') split ( ))  select bus_no,         bus_route,        stuff((select  ',' + c2.bus_stop               cte c2               c1.bus_no = c2.bus_no , bus_stop '%s%'               xml path ('')), 1, 1, '') bus_stop cte c1 bus_stop '%s%' group bus_no, bus_route 

output:

bus_no  bus_route   bus_stop 1       1           apsara,shahadara,shakarpur 2       2           amsterdam,brussels,bruges,brussels