//User Defined Function to Split the number series into temp results data
Create FUNCTION [dbo].[Split] (@sep char(1), @s varchar(5000))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn AS SerialNumber,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 5000 END) AS IDValue
FROM Pieces
)
Declare @NumberToFind int= 14
Select T.Id From Table T Where ISNULL(T.ConcatStringColumn ,'') != ''
And Exists (Select IDValue From dbo.Split(',',T.ConcatStringColumn ) Where IDValue =@NumberToFind)
Output:
'Some ID Here If specific number finds from that string'
