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