|
|
Free Software, Source Code, and Best-Practice Documentation Index from your
Cleveland Software, Database, Web Design, Consulting, and SQL / Delphi Experts
|
SQL Server - User Defined Function:
Parse / Split string of delimited Integers into Table
|
An incredibly useful function 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 delmited string of integer values to this function,and join to the results of this function.
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 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.
-- will need to be changed if the delimiter on the front end is ever changed to anything else.
--
-- The function also selects any "missing" values as NULL. I.e., if the 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.
--
-- There is no error testing for malformed strings that contain non-numeric values other than the
-- defined delimiter.
--
-- TEST CASES:
-- ===========
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable]('1', ',')
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable]('1,', ',')
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable]('1,2', ',')
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable]('1,2,', ',')
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable]('1,2,0', ',')
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable]('', ',')
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable](NULL, ',')
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable](',', ',')
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable](',,,,', ',')
--SELECT * FROM dbo.[udfGetDelimitedIntegerListAsTable]('1,2', '|') --Conversion failed when converting the varchar value '1,2' to data type int.
/**********************************************************************************************/
CREATE FUNCTION [dbo].[udfGetDelimitedIntegerListAsTable]
(
@IntegerListString VARCHAR(8000), -- Use VARCHAR(MAX) instead of VARCHAR(8000) for SQL Server 2005
@delimiter CHAR(1)
)
RETURNS @IntegersTable TABLE (IntegerFromList INT)
AS
BEGIN
IF @IntegerListString IS NULL
SELECT @IntegerListString = ''
IF LEN(@IntegerListString) > 0
BEGIN
DECLARE @iDelimiterPosition INT
SELECT @iDelimiterPosition = PATINDEX('%' + @delimiter + '%', @IntegerListString)
WHILE @iDelimiterPosition <> 0
BEGIN
--If nothing between delims, save as NULL
IF LEN(SUBSTRING(@IntegerListString, 1, @iDelimiterPosition - 1)) = 0
INSERT INTO @IntegersTable(IntegerFromList) VALUES (NULL)
ELSE
INSERT INTO @IntegersTable(IntegerFromList)
VALUES (CONVERT(INT, SUBSTRING(@IntegerListString, 1, @iDelimiterPosition - 1)))
SELECT @IntegerListString = SUBSTRING(@IntegerListString, @iDelimiterPosition + 1, LEN(@IntegerListString))
SELECT @iDelimiterPosition = PATINDEX('%' + @delimiter + '%', @IntegerListString)
END --While...
--If nothing after final delims, save as NULL
IF LEN(@IntegerListString) = 0
INSERT INTO @IntegersTable(IntegerFromList) VALUES (NULL)
ELSE
INSERT INTO @IntegersTable(IntegerFromList)
VALUES(CONVERT(INT, @IntegerListString))
END --IF...
RETURN
END --Function |
|
