SQL Query: find the specific number from the number series stored as record in a Column from the DB Table

//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'