MS SQL-Server Function: Parse / Split string of delimited Strings into Table

SQL SERVER Source Code — User Defined Function (UDF): Parse / Split string of delimited Strings into Table (as Rows)

A Microsoft SQL-Server Transact-SQL (T-SQL) user-defined function (UDF) that is an incredibly useful function for splitting a single string that contains sub-strings of delimited string values into a table result (with each substring becoming a table row value in a returned table).

This is very handy if you have a list of string values that you want to insert into a table or if you wish to retrieve results from a table for (where there are values corresponding to the one or more values in your delimited string).  Just pass a concatenated and delimited string of string values to this function, and join to the results of this function.

For example, @vStrings is a string of comma-separated values, like 'abc,def,hij,name1,name2' :

CREATE PROCEDURE uspDoWhatever
@vStrings VARCHAR(max)
AS

SELECT [column list] FROM [sometable]
WHERE [strcolumnname] IN (SELECT * from dbo.[udfGetDelimitedStringListAsTable](@vStrings, ','))

SQL-Server User Defined Function (UDF) Source Code

--********************************************************************************
--This source code is Copyright (c) 2007-2017
--     Author: Mike Eberhart
--
--I hereby release this code under the terms of the MIT License (for freeware).
--
--Permission is hereby granted, free of charge, to any person obtaining a copy
--of this software and associated documentation files (the "Software"), to deal
--in the Software without restriction, including without limitation the rights
--to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
--copies of the Software, and to permit persons to whom the Software is
--furnished to do so, subject to the following conditions:
--
--The above copyright notice and this permission notice shall be included in
--all copies or substantial portions of the Software.
--
--THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
--IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
--FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
--AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
--LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
--OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
--THE SOFTWARE.
--********************************************************************************
--**********************************************************************************************
-- This function takes a string of delimited strings and explodes them out into a table.
-- Each string within the StringList parm is NOT quoted internally - this allows any embedded 
-- quotes to be preserved during parsing.
--   
-- By default, the delimiter is set to '|' if another is not specified in the second parameter.
--
-- The function also selects any "missing" values as NULL.
-- E.g., if a comma-delimited string to parse is 'A,,,B' the function assumes each (missing) 
-- value between consecutive commas represents a NULL value.
-- The calling code can turn NULLs into whatever is needed.
--
-- SOME EXAMPLES / TEST CASES:
-- ===========================
--SELECT * FROM dbo.[udfGetDelimitedStringListAsTable](NULL, ',');        --No rows returned
--SELECT * FROM dbo.[udfGetDelimitedStringListAsTable](NULL, NULL);       --No rows returned
--SELECT * FROM dbo.[udfGetDelimitedStringListAsTable]('A', NULL);        --No rows returned
--SELECT * FROM dbo.[udfGetDelimitedStringListAsTable]('', ',');          --No rows returned
--SELECT * FROM dbo.[udfGetDelimitedStringListAsTable]('A', DEFAULT);     --Returns ONE string
--SELECT * FROM dbo.[udfGetDelimitedStringListAsTable]('A,B,C', DEFAULT); --Returns ONE string
--SELECT * FROM dbo.[udfGetDelimitedStringListAsTable]('A|B|C', DEFAULT); --Returns THREE strings
--SELECT * FROM dbo.[udfGetDelimitedStringListAsTable]('1,', ',');        --Return '1' and NULL
--SELECT * FROM dbo.[udfGetDelimitedStringListAsTable]('1,2,', ',');      --Two strings and NULL
--SELECT * FROM dbo.[udfGetDelimitedStringListAsTable](',', ',');         --Return TWO NULLs
----Now a couple tests using embedded quotes and more...
--SELECT * FROM dbo.[udfGetDelimitedStringListAsTable]('''abcde'',1234|', '|');
--SELECT * FROM dbo.[udfGetDelimitedStringListAsTable]('''abcd%''|1234', '|');
--**********************************************************************************************
CREATE FUNCTION [udfGetDelimitedStringListAsTable]
(
  @StringList VARCHAR(MAX),
  @Delimiter CHAR(1) = '|'
)
RETURNS @StringsTable TABLE (StringFromList VARCHAR(100)) --NOTE: size column to fit your needs.
AS
BEGIN
  IF (@StringList IS NULL) OR (LEN(@StringList) = 0) OR (@Delimiter IS NULL) RETURN;
  
  DECLARE @DelimPos INT = PATINDEX('%' + @Delimiter + '%', @StringList);
  
  WHILE @DelimPos <> 0
  BEGIN
    --If nothing between delims, save as NULL
    IF LEN(SUBSTRING(@StringList, 1, @DelimPos - 1)) = 0
      INSERT INTO @StringsTable(StringFromList) VALUES(NULL);
    ELSE
      INSERT INTO @StringsTable(StringFromList)
        VALUES(SUBSTRING(@StringList, 1, @DelimPos - 1));
    
    SET @StringList = SUBSTRING(@StringList, @DelimPos + 1, LEN(@StringList));
    SET @DelimPos = PATINDEX('%' + @Delimiter + '%', @StringList);
  END; --While...
  
  --If no additional chars after a final delim, treat as an additional NULL
  IF LEN(@StringList) = 0
    INSERT INTO @StringsTable(StringFromList) VALUES(NULL)
  ELSE
    INSERT INTO @StringsTable(StringFromList) VALUES(@StringList);
  
  RETURN; 
  
END --Function

Continue to read this Software Development and Technology Blog for computer programming, software development, and technology Techniques, How-To's, Fixes, Reviews, and News — focused on Dart Language, SQL Server, Nvidia CUDA, VMware, TypeScript, SVG, other technology tips and how-to's, and my varied political and economic opinions.