MSSQL - Convert list to table #2 (array of string items)

Often, when working with databases there is a need to pass a parameter with set of elements.
Function described here (TABLE-Value UDF) converts a string in the form "aaa;bbb;ccc" to tabular form.
Additionally could be removed empty items.
In case if list has non-unique items, the function returns the serial number for each items group (RANK).

-- Drop Function if exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.tf_array2table'))
    DROP FUNCTION dbo.tf_array2table;
GO
/*=============================================
Author:
    Yuri Abele
Changes:
    11.06.2013 - Yuri Abele - initial
Description:
    Function to convert list of Values to Table
 
Usage: Get all items
    SELECT item_index, item_value = IIF(item_value = '', '---', item_value), item_rank
    FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 0);
Usage: Get all non-empty items
    SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1);
Usage: Get all non-empty items and filter non-unique
    SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1)
    WHERE item_rank=1;
=============================================*/
CREATE FUNCTION dbo.tf_array2table
(
    @array NVARCHAR(MAX), -- List of delimited values
    @delim NCHAR(1),      -- Delimiter
    @remove_empty BIT     -- Flag to remove empty values
)
-- Container for Array Items
RETURNS @data TABLE(
    item_index INT IDENTITY(1,1), -- 1-based index of item
    item_value NVARCHAR(MAX),     -- item value
    item_rank  INT                -- item rank if items are not unique
)
AS BEGIN
    -- Container for XML
    DECLARE
        @xml_text NVARCHAR(MAX),
        @xml XML;
 
    IF @remove_empty = 1 BEGIN
        -- Remove empty inner items
        WHILE (CHARINDEX(@delim + @delim, @array, 0) > 0) BEGIN
            SET @array = REPLACE(@array, @delim + @delim, @delim);
        END;
        -- Remove empty left item
        IF(LEFT(@array, 1) = @delim) BEGIN
            SET @array = SUBSTRING(@array, 2, LEN(@array)-1)
        END;
        -- Remove empty right item
        IF(RIGHT(@array, 1) = @delim) BEGIN
            SET @array = SUBSTRING(@array, 1, LEN(@array)-1)
        END;
    END;
    
    -- Prepare XML-Text
    SET @xml_text = N'<L><I>' +
        REPLACE(@array, @delim, N'</I><I>') +
        N'</I></L>';
    
    -- Convert Array to XML
    SET @xml = CAST(@xml_text AS XML);
 
    DECLARE @temp_data TABLE(
        item_index INT IDENTITY(1,1),
        item_value NVARCHAR(MAX)
    )
    -- Extract Array Items to Table-Variable
    INSERT INTO @temp_data(item_value)
    SELECT
        item_value = item.value('.', 'NVARCHAR(MAX)')
    FROM
        @xml.nodes('//I') XMLDATA(item);
    
    -- Calculate Rank for each item (to find non-unique items)
    INSERT INTO @data(item_value, item_rank)
    SELECT
        item_value,
        item_rank=RANK() OVER(PARTITION BY item_value ORDER BY item_index, item_value)
    FROM
        @temp_data
    ORDER BY
        item_index;
    
    RETURN;
END;
GO

Usages:

-- Get all items
SELECT item_index, item_value = IIF(item_value = '', '---', item_value), item_rank
FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 0);

Result

item_index	item_value	item_rank
1	---	1
2	ddd	1
3	bbb	1
4	ccc	1
5	---	2
6	---	3
7	ccc	2
8	ddd	2
9	eee	1
10	---	4

-- Get all non-empty items
SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1);

Result

item_index	item_value	item_rank
1	ddd	1
2	bbb	1
3	ccc	1
4	ccc	2
5	ddd	2
6	eee	1

-- Get all non-empty items and filter non-unique
SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1)
WHERE item_rank=1;

Result

item_index	item_value	item_rank
1	ddd	1
2	bbb	1
3	ccc	1
6	eee	1

MSSQL - Convert list to table #1 (array of numbers in string form)

Extended version of function from this post: MSSQL - Convert list to table #2 (array of string items)
Function described here (TABLE-Value UDF) converts a string in the form "123;bbb;;;456" to tabular form.
Result will contain only non-empty items which was possible to convert to INTEGER.
In case if list has non-unique items, the function returns the serial number for each items group (RANK).

-- Drop Function if exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.fnArray2IntTable'))
    DROP FUNCTION dbo.fnArray2IntTable;
GO

/*=============================================
Author:
    Yuri Abele
Changes:
    11.06.2013 - Yuri Abele - initial
Description:
    Function to convert list of numeric Values to Table of integers
Remark:
    Empty and non-numeric values will be ignored

Usage: Get all items
    SELECT * FROM dbo.fnArray2IntTable(N';444;bbb;333;;;333;444;555;', N';');
Usage: Get all non-empty items and filter non-unique
    SELECT * FROM dbo.fnArray2IntTable(N';444;bbb;333;;;333;444;555;', N';') WHERE ItemRank=1;
=============================================*/
CREATE FUNCTION dbo.fnArray2IntTable(
    @Array NVARCHAR(MAX),
    @Delim NCHAR(1)
)
-- Container for Array Items
RETURNS @Data TABLE(
    ItemIndex INT IDENTITY(1,1),
    ItemValue INT,
    ItemRank INT
)
AS BEGIN
    -- Container for XML
    DECLARE
        @XmlText NVARCHAR(MAX),
        @Xml XML;
    
    -- Remove empty inner items
    WHILE (CHARINDEX(@Delim + @Delim, @Array, 0) > 0) BEGIN
        SET @Array = REPLACE(@Array, @Delim + @Delim, @Delim);
    END;
    -- Remove empty left item
    IF(LEFT(@Array, 1) = @Delim) BEGIN
        SET @Array = SUBSTRING(@Array, 2, LEN(@Array)-1)
    END;
    -- Remove empty right item
    IF(RIGHT(@Array, 1) = @Delim) BEGIN
        SET @Array = SUBSTRING(@Array, 1, LEN(@Array)-1)
    END;
    
    -- Prepare XML-Text
    SET @XmlText = N'<List><Item>' +
        REPLACE(@Array, @Delim, N'</Item><Item>') +
        N'</Item></List>';
    
    -- Convert Array to XML
    SET @Xml = CAST(@XmlText AS XML);
    
    -- Temp Table-Variableble
    DECLARE @TempData TABLE(
        ItemIndex INT IDENTITY(1,1),
        ItemValue NVARCHAR(MAX)
    )

    -- Extract Array Items to temp Table-Variable
    INSERT INTO @TempData
    SELECT
        Item = item.value('.', 'INT')
    FROM
        @Xml.nodes('//Item') XMLDATA(item)
    WHERE
        -- Skeep non-numeric items
        ISNUMERIC(item.value('.', 'NVARCHAR(MAX)')) = 1;
    
    -- Calculate Rank for each item (to find non-unique items)
    INSERT INTO @Data(ItemValue, ItemRank)
    SELECT
        ItemValue,
        ItemRank=RANK() OVER(PARTITION BY ItemValue ORDER BY ItemIndex, ItemValue)
    FROM
        @TempData
    ORDER BY
        ItemIndex;

    RETURN;
END;

GO

-- Get all non-empty and numeric items
SELECT * FROM dbo.fnArray2IntTable(N';444;bbb;333;;;333;444;555;', N';');
-- Get all non-empty and numeric items and filter non-unique
SELECT * FROM dbo.fnArray2IntTable(N';444;bbb;333;;;333;444;555;', N';') WHERE ItemRank=1;

Result of execution: