I’ve got this SQL Function to parse comma separated value into a table. We need this function when our application commit separated comma value in a text. We normally do this when we want to send an array of value to the SQL server

CREATE FUNCTION [dbo].[SplitCSV](@CSV text)
-- Returns a table with txtValue column
RETURNS @OutTable table(txtValue text)
AS
BEGIN

declare @currentposition int
declare @nextposition    int
declare @lengthOfString  int

-- Assign the starting position of the string
SET @currentposition = 0
 -- The reason for this is to force entrance into the while loop below.
SET @nextposition = 1

WHILE @nextposition > 0
BEGIN
-- Assign the next position to be the current index of ',' + 1
SELECT @nextposition = charindex(',', @CSV, @currentposition + 1)

-- In here we need to find 2 things. The position of the ','
-- and the length of the string segment in between.
SELECT @lengthOfString = CASE WHEN @nextposition > 0
       THEN @nextposition
       ELSE DATALENGTH(@CSV) + 1
       END - @currentposition - 1

--After the length and position is found all we need to do
--is take the substring of the string passed in.
INSERT @OutTable (txtValue)
       VALUES (substring(@CSV, @currentposition + 1, @lengthOfString))

--Set the current position to the next position
SELECT @currentposition = @nextposition
END
RETURN
END

Usage in SQL Query

SELECT
	VenueID
FROM
	Venues
WHERE
	PostCode
IN
	(SELECT CAST(txtValue as VarChar) FROM dbo.splitcsv(@PostCode))