I work with data from a system that stores values as arrays or extents. In 2008, I created a CLR function to expose the C# split command. I tried several iteration with loops in T-SQL, etc. overtime, I have noticed the CLR as being a bit of a memory hog.
I found this possible solution : Split a string in TSQL (without looping).
Need to change it a bit ..
1. Reduce the numbers table to MAX Extents position ( 80). Still populated it with 1-120.. just in case.
2. Needed it to return as an INLINE Scalar Value and not a Table Value Function.
Here is What I Came up with from the original Script:
/*
Adapted from Split a string in TSQL (without looping)
http://blog.magenic.com/blogs/whitneyw/archive/2008/10/08/Split-a-string-in-TSQL-_2800_without-looping_2900_.aspx
(Link no longer valid)
JCD Changes 5/2010
1. Only have need for 80 position Arrays..
So, I limited my Numbers Table to 120
2. Added a @pos INT to evaluate only one position
3. Need it to act as a SCALAR Function
and not a a Table value Funtion
*/
– ORIGINAL COMMENT
– Create and populate an auxiliary
– table of numbers
IF OBJECT_ID(‘dbo.Numbers’) IS NOT NULL
BEGIN
DROP TABLE dbo.Numbers;
END
GO
CREATE TABLE dbo.Numbers
(
number INT NOT NULL
CONSTRAINT PK_Numbers
PRIMARY KEY CLUSTERED
);
SET NOCOUNT ON;
DECLARE
@max AS INT,
@rc AS INT;
SET @max = 120; — you may need to set this much higher
SET @rc = 1;
BEGIN TRAN;
INSERT INTO dbo.Numbers(number) VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Numbers(number)
SELECT number + @rc
FROM dbo.Numbers;
SET @rc = @rc * 2;
END
INSERT INTO dbo.Numbers(number)
SELECT number + @rc
FROM dbo.Numbers
WHERE number + @rc <= @max;
COMMIT TRAN;
GO
/*
–ORIGINAL COMMENT
Next, we create the inline function that
will do the heavy lifting for us. I’ve added
an input for separator as hard coding the comma
will almost always guarantee some other delimiter
will be needed.
Turned it into a scalar function
*/
IF OBJECT_ID(‘dbo.SplitString’) IS NOT NULL
DROP FUNCTION dbo.SplitString;
GO
CREATE FUNCTION dbo.SplitString(@arr AS VARCHAR(MAX), @sep AS CHAR(1),@pos AS INT )
RETURNS VARCHAR(60)
AS
BEGIN
BEGIN — SET Value
DECLARE @result VARCHAR(60)
— Changed to put return one value to a variable
SELECT
@result = RTRIM(LTRIM(SUBSTRING(@arr, number, CHARINDEX(@sep, @arr + @sep, number) – number)))
FROM dbo.Numbers
WHERE number <= LEN(@arr) + 1
AND SUBSTRING(@sep + @arr, number, 1) = @sep
AND ((number – 1) – LEN(REPLACE(LEFT(@arr, number – 1), @sep, ”)) + 1) = @pos;
END
RETURN RTRIM(@Result)
END
GO
/*
–ORIGINAL COMMENT
Finally, we call our function with a string of values.
If we needed to then utilize this for filtering we could use
the CROSS APPLY operator introduced in SQL 2005.
*/
DECLARE @arr varchar(MAX),
@sep char(1),
@pos INT;
SELECT @arr = ’1, 2, Dan, 4, Blah, %, 7, “We the people…”, 9,”A string walks into a RBAR and says…”, B, C’
,@sep = ‘,’ — Set the delimiter
,@pos = 6 — Set the position to return
– Return Single Value
SELECT dbo.SplitString(@arr, @sep, @pos) as SplitValue;
SELECT dbo.SplitString(@arr, @sep, 8) as SplitValue;
SELECT dbo.SplitString(@arr, @sep, 10) as SplitValue;
GO