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 |
No comments:
Post a Comment
Note: only a member of this blog may post a comment.