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: