SQL Table Valued Functions

*********** Comma separted string convert to table *************

USE [Nobolo]

GO

/****** Object:  UserDefinedFunction [dbo].[FnSplitComma]    Script Date: 7/8/2020 1:27:20 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[FnSplitComma] (@InStr VARCHAR(MAX))

RETURNS @TempTab TABLE

   (ids varchar(500) not null)

AS

BEGIN

    ;-- Ensure input ends with comma

SET @InStr = REPLACE(@InStr + ',', ',,', ',')

DECLARE @SP INT

DECLARE @VALUE VARCHAR(1000)

WHILE PATINDEX('%,%', @INSTR ) <> 0 

BEGIN

   SELECT  @SP = PATINDEX('%,%',@INSTR)

   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)

   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')

   INSERT INTO @TempTab(ids) VALUES (@VALUE)

END

RETURN

END

******************************************************