You are paying the cost of bad database design. The values should be in separate records and not jammed into one field.
You could extract the record and then create a child collection in your business logic layer using a split function.
You could create a sub query in the database that does the same thing, I suggest you use 2 fields, the primary key and ONE of the values per record using a split function.
An old split function
CREATE FUNCTION [dbo].[fn_Split]
(@List VARCHAR(8000), @Delimiter CHAR(1))
RETURNS @Results TABLE
(Item VARCHAR(8000),ID INT IDENTITY(1,1))
AS
BEGIN
DECLARE @IndexStart INT
DECLARE @IndexEnd INT
DECLARE @Length INT
DECLARE @Word VARCHAR(8000)
SET @IndexStart = 1
SET @IndexEnd = 0
SET @Length = LEN(@List)
IF @Delimiter = '' SET @Delimiter = ','
SET @List = REPLACE(@List,CHAR(9),'')
SET @List = REPLACE(@List,CHAR(10),'')
SET @List = REPLACE(@List,CHAR(13),'')
WHILE @IndexStart <= @Length
BEGIN
SET @IndexEnd = CHARINDEX(@Delimiter, @List, @IndexStart)
IF @Delimiter = CHAR(32)
SET @IndexEnd = CHARINDEX(SPACE(1), @List, @IndexStart)
IF @IndexEnd = 0
SET @IndexEnd = @Length + 1
SET @Word = SUBSTRING(@List, @IndexStart, @IndexEnd - @IndexStart)
SET @IndexStart = @IndexEnd + 1
INSERT INTO @Results(Item)
SELECT @Word
END
RETURN
END
GO
Usage
DECLARE @VALUES VARCHAR(1000)
SET @Values = 'Shop1,Shop2,Shop3,Shop4,Shop5,Shop6,Shop7 '
SELECT 1001 AS PrimaryKey, item AS Vendor
from dbo.fn_Split(@VALUES,',')