TSQL function to split comma delimited string and return it as a table

Here is a handy TSQL function that will take comma delimited string, split it, and return values as a table.

CREATE FUNCTION [dbo].[fnSplitStringToInt]
(
	@input varchar(MAX)
)
RETURNS @return TABLE ([ID] int)
AS
BEGIN

	DECLARE @ID int
	DECLARE @position int
	
	--Check if there is only one element
	IF (CHARINDEX(',', @input) = 0 AND LEN(@input) > 0)
	BEGIN
		INSERT INTO @return
		SELECT @input
		
		RETURN
	END

	WHILE CHARINDEX(',', @input) > 0
	BEGIN
		SELECT @position  = CHARINDEX(',', @input)  
		SELECT @ID = SUBSTRING(@input, 1, @position - 1)

		INSERT INTO @return
		SELECT @ID

		SELECT @input = SUBSTRING(@input, @position + 1, LEN(@input) - @position)
	END
	
	-- Last element
	IF (CHARINDEX(',', @input) = 0 AND LEN(@input) > 0)
	BEGIN
		INSERT INTO @return
		SELECT @input
		
		RETURN
	END

	RETURN
END
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s