SQL SERVER Function (UDF) to Parse / Split string of delimited Integers into Table

SQL Server UDF to Parse / Split string of delimited Integers into Table

Here is an incredibly useful Microsoft SQL-Server Transact-SQL (T-SQL) user-defined function (UDF) for splitting a string of delimited integer values into a table result.

This is very handy if you have a list of integer index or primary-key values that you want to retrieve results from a table for. Just pass a concatenated and delimited string of integer values to this function, and JOIN to the results of this function or use IN within the SELECT condition, etc.

For example, @vIDs is a string of comma-separated values, like ''1,2,4,22,101'' :

CREATE PROCEDURE uspDoWhatever
    @vIDs    VARCHAR(8000)
AS

SELECT [column list]
  FROM [SomeTable]
  WHERE [intColumnName] IN (SELECT * from dbo.udfGetDelimitedIntegerListAsTable(@vIDs, '',''))

This comes in very handy when working with one-to-many relationships in UIs, whether web-based or otherwise, and other applications.

I will add links here to additional related functions and procedures.

SQL-Server User Defined Function (UDF) Source Code

--********************************************************************************
--This source code is Copyright (c) 2007-2016
--     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 numbers and explodes them out into a table.  
-- 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 '1,,,2' the function assumes each (missing) 
-- value between consecutive commas represents a NULL value.
-- The calling code can turn NULLs into whatever is needed.
--
-- NOTE:There is no error testing for malformed strings that contain non-numeric values other 
-- than the defined delimiter.  Also, currently just setup for INT values at max (vs. BIGINT).
--
-- SOME EXAMPLES / TEST CASES:
-- ===========================
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable](NULL, ',');       --No rows returned
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable](NULL, NULL);      --No rows returned
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable]('1', NULL);       --No rows returned
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable]('', ',');         --No rows returned
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable]('1', DEFAULT);    --Returns ONE value
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable]('1|2|3', DEFAULT);--Returns THREE values
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable]('1,2,3', ',');    --Returns THREE values
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable]('1,', ',');       --Return '1' and NULL
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable]('1,2,', ',');     --Two values and NULL
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable](',', ',');        --Return TWO NULLs
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable]('1,2,3', DEFAULT);--Conversion Error!
--**********************************************************************************************
CREATE FUNCTION [udfGetDelimitedIntegerListAsTable]
(
  @IntegerList  VARCHAR(MAX),
  @Delimiter    CHAR(1) = '|'
)
RETURNS @IntegersTable TABLE (IntegerFromList  INT)
AS
BEGIN
  IF (@IntegerList IS NULL) OR (LEN(@IntegerList) = 0) OR (@Delimiter IS NULL) RETURN;

  DECLARE  @DelimPos INT = PATINDEX('%' + @Delimiter + '%', @IntegerList);

  WHILE @DelimPos <> 0
  BEGIN
    --If nothing between delims, save as NULL
    IF LEN(SUBSTRING(@IntegerList, 1, @DelimPos - 1)) = 0
      INSERT INTO @IntegersTable(IntegerFromList)  VALUES(NULL);
    ELSE
      INSERT INTO @IntegersTable(IntegerFromList)
      VALUES(CONVERT(INT, SUBSTRING(@IntegerList, 1, @DelimPos - 1)));

    SET @IntegerList = SUBSTRING(@IntegerList, @DelimPos + 1, LEN(@IntegerList));
    SET @DelimPos = PATINDEX('%' + @Delimiter + '%', @IntegerList);
  END --While...

  --If no additional chars after a final delim, treat as an additional NULL
  IF LEN(@IntegerList) = 0
    INSERT INTO @IntegersTable(IntegerFromList)  VALUES(NULL);
  ELSE
    INSERT INTO @IntegersTable(IntegerFromList)  VALUES(CONVERT(INT, @IntegerList));

  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.