MSSQL Function to Split Comma Delimited String

Tuesday, October 23rd, 2012 at 8:56 am | How to, SQL


Whenever I seem to have to develop an automated ETL process, particularly when dealing with company financials, it involved taking a payment file, which is full of numbers that are comma delimited, and inserting it into an SQL table, so we can perform some data cleansing, reporting, and loading into financial systems.

For the sake of brevity, I’ll just keep this as simple as possible. Let’s just create a dummy table, and put some example data into it:

CREATE TABLE example (Data VARCHAR(1000))

INSERT INTO example
VALUES ('10,20,30,40')

INSERT INTO example
VALUES ('30,40,10,20')

INSERT INTO Example
VALUES ('20,30,10,40')

INSERT INTO Example
VALUES ('40,10,20,30')

So now you’ll have some dummy data in a table example, that will be a single string but will have comma’s separating each value.

Create the below function:

CREATE FUNCTION dbo.fnSplit (
	@Expression NVARCHAR(max)
	,@Delimiter NVARCHAR(max)
	,@INDEX INT
	)
RETURNS NVARCHAR(max)
AS
BEGIN
	DECLARE @RETURN NVARCHAR(max)
	DECLARE @Pos INT
	DECLARE @PrevPos INT
	DECLARE @I INT

	IF @Expression IS NULL
		OR @Delimiter IS NULL
		OR LEN(@Delimiter) = 0
		OR @INDEX < 1
		SET @RETURN = NULL
	ELSE
		IF @INDEX = 1
		BEGIN
			SET @Pos = CHARINDEX(@Delimiter, @Expression, 1)

			IF @Pos > 0
				SET @RETURN = LEFT(@Expression, @Pos - 1)
		END
		ELSE
		BEGIN
			SET @Pos = 0
			SET @I = 0

			WHILE (
					@Pos > 0
					AND @I < @INDEX
					)
				OR @I = 0
			BEGIN
				SET @PrevPos = @Pos
				SET @Pos = CHARINDEX(@Delimiter, @Expression, @Pos + LEN(@Delimiter))
				SET @I = @I + 1
			END

			IF @Pos = 0
				AND @I = @INDEX
				SET @RETURN = SUBSTRING(@Expression, @PrevPos + LEN(@Delimiter), LEN(@Expression))
			ELSE
				IF @Pos = 0
					AND @I <> @INDEX
					SET @RETURN = NULL
				ELSE
					SET @RETURN = SUBSTRING(@Expression, @PrevPos + LEN(@Delimiter), @Pos - @PrevPos - LEN(@Delimiter))
		END

	RETURN @RETURN
END

Use this function like this:

SELECT dbo.fnSplit('Data', ',', 2) AS SecondAmount
	,dbo.fnSplit('Data', ',', 4) AS FourthAmount
FROM example

This will give you:

SecondAmount,FourthAmount
20,40
40,20
30,40
10,30

Leave a Reply

 

About Me

Hi, my name is Michael, I’m a Gold Coast based IT Professional specialising in Systems, Network, and Database Administration, Software Development and Web Systems, a massive Star Wars fanboy, musician and gamer. I am the creator of Simple Silent Mode Toggle for Android.

 
 
 

30% off New Products from GoDaddy!

© Copyright 2014 Michael Morley All rights reserved.