Split a string in TSQL (without looping)<\/a>.\u00a0<\/p>\nNeed to change it a bit ..\u00a0<\/p>\n
1. Reduce the numbers table to MAX Extents position ( 80). Still populated it with 1-120.. just in case.
\n2. Needed it to return as an INLINE Scalar Value and not a Table Value Function.\u00a0<\/p>\n
Here is What I Came up with from the original\u00a0 Script:<\/p>\n
\/*
\nAdapted from Split a string in TSQL (without looping)
\nhttp:\/\/blog.magenic.com\/blogs\/whitneyw\/archive\/2008\/10\/08\/Split-a-string-in-TSQL-_2800_without-looping_2900_.aspx
\n(Link no longer valid)
\nJCD Changes 5\/2010
\n 1. Only have need for 80 position Arrays..
\n\tSo, I limited my Numbers Table to 120
\n 2. Added a @pos INT to evaluate only one position
\n 3. Need it to act as a SCALAR Function
\n\tand not a a Table value Funtion<\/p>\n
*\/
\n— ORIGINAL COMMENT
\n— Create and populate an auxiliary
\n— table of numbers <\/p>\n
IF OBJECT_ID(‘dbo.Numbers’) IS NOT NULL
\nBEGIN
\n DROP TABLE dbo.Numbers;
\nEND
\nGO
\nCREATE TABLE dbo.Numbers
\n(
\n number INT NOT NULL
\n CONSTRAINT PK_Numbers
\n PRIMARY KEY CLUSTERED
\n);<\/p>\n
SET NOCOUNT ON;<\/p>\n
DECLARE
\n @max AS INT,
\n @rc AS INT;<\/p>\n
SET @max = 120; — you may need to set this much higher
\nSET @rc = 1;<\/p>\n
BEGIN TRAN;
\n INSERT INTO dbo.Numbers(number) VALUES(1);<\/p>\n
WHILE @rc * 2 <= @max\n BEGIN\n INSERT INTO dbo.Numbers(number)\n SELECT number + @rc\n FROM dbo.Numbers;\n\n SET @rc = @rc * 2;\n END\n\n INSERT INTO dbo.Numbers(number)\n SELECT number + @rc\n FROM dbo.Numbers\n WHERE number + @rc <= @max;\nCOMMIT TRAN;\n\nGO\n\n\/*\n--ORIGINAL COMMENT\nNext, we create the inline function that \nwill do the heavy lifting for us. I've added \nan input for separator as hard coding the comma\nwill almost always guarantee some other delimiter \nwill be needed.\n\nTurned it into a scalar function\n\n*\/\nIF OBJECT_ID('dbo.SplitString') IS NOT NULL\nDROP FUNCTION dbo.SplitString;\nGO\nCREATE FUNCTION dbo.SplitString(@arr AS VARCHAR(MAX), @sep AS CHAR(1),@pos AS INT )\n RETURNS VARCHAR(60)\nAS\nBEGIN\n BEGIN -- SET Value\n DECLARE @result VARCHAR(60)\n -- Changed to put return one value to a variable\n SELECT\n @result = RTRIM(LTRIM(SUBSTRING(@arr, number, CHARINDEX(@sep, @arr + @sep, number) - number)))\n FROM dbo.Numbers\n WHERE number <= LEN(@arr) + 1\n AND SUBSTRING(@sep + @arr, number, 1) = @sep\n AND ((number - 1) - LEN(REPLACE(LEFT(@arr, number - 1), @sep, '')) + 1) = @pos;\n END\n\n RETURN RTRIM(@Result)\nEND\nGO\n\n\/*\n--ORIGINAL COMMENT\nFinally, we call our function with a string of values.\nIf we needed to then utilize this for filtering we could use\nthe CROSS APPLY operator introduced in SQL 2005.\n\n*\/\n\nDECLARE @arr varchar(MAX),\n @sep char(1),\n @pos INT;\nSELECT @arr = '1, 2, Dan, 4, Blah, %, 7, \"We the people...\", 9,\"A string walks into a RBAR and says...\", B, C'\n ,@sep = ',' -- Set the delimiter\n ,@pos = 6 -- Set the position to return \n-- Return Single Value\nSELECT dbo.SplitString(@arr, @sep, @pos) as SplitValue;\nSELECT dbo.SplitString(@arr, @sep, 8) as SplitValue;\nSELECT dbo.SplitString(@arr, @sep, 10) as SplitValue;\nGO\n\n<\/p>\n","protected":false},"excerpt":{"rendered":"
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 …<\/p>\n
Read more »<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[138,113,22,5],"tags":[262,291],"class_list":["post-143","post","type-post","status-publish","format-standard","hentry","category-programming","category-reportingbi","category-sql-server","category-t-sql","tag-clr","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/143","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=143"}],"version-history":[{"count":10,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/143\/revisions"}],"predecessor-version":[{"id":304,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/143\/revisions\/304"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=143"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=143"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=143"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}