Thursday, November 26, 2015

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'




No comments:

Post a Comment