Hi @Marc Miller ,
Welcome to the microsoft TSQL Q&A forum!
Please refer to:
1.Use string_split(applies to SQL Server 2016 and later):
CREATE TABLE yourtable (
rec_id int,
[desc] varchar(20),
stores varchar(20)
);
INSERT INTO yourtable VALUES(1,'abc','''1'',''3'''),(2,'def','''1'',''2'',''3'''),
(3,'ghi','''2'',''4''');
DECLARE @var varchar(3)
SET @var ='''2'''
SELECT * FROM (SELECT *
FROM yourtable y
CROSS APPLY STRING_SPLIT(y.stores, ',')) t
WHERE [value]=@var;
2.Create user-defined functions(applies to SQL Server (all supported versions))
CREATE FUNCTION SplitStr(@Sourcestr VARCHAR(8000), @Seprate VARCHAR(100))
RETURNS @result TABLE(F1 VARCHAR(100))
AS
BEGIN
DECLARE @sql AS VARCHAR(100)
SET @Sourcestr=@Sourcestr+@Seprate
WHILE(@Sourcestr<>'')
BEGIN
SET @sql=left(@Sourcestr,CHARINDEX(',',@Sourcestr,1)-1)
INSERT @result VALUES(@sql)
SET @Sourcestr=STUFF(@Sourcestr,1,CHARINDEX(',',@Sourcestr,1),'')
END
RETURN
END
GO
DECLARE @var varchar(3)
SET @var ='''2'''
SELECT * FROM (SELECT *
FROM yourtable y
CROSS APPLY SplitStr(y.stores,',')) t
WHERE [F1]=@var;
DROP FUNCTION SplitStr
If you have any question, please feel free to let me know.
Regards
Echo
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.