Tuesday, 11 June 2013

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).
  1. -- Drop Function if exists
  2. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.tf_array2table'))
  3. DROP FUNCTION dbo.tf_array2table;
  4. GO
  5. /*=============================================
  6. Author:
  7. Yuri Abele
  8. Changes:
  9. 11.06.2013 - Yuri Abele - initial
  10. Description:
  11. Function to convert list of Values to Table
  12. Usage: Get all items
  13. SELECT item_index, item_value = IIF(item_value = '', '---', item_value), item_rank
  14. FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 0);
  15. Usage: Get all non-empty items
  16. SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1);
  17. Usage: Get all non-empty items and filter non-unique
  18. SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1)
  19. WHERE item_rank=1;
  20. =============================================*/
  21. CREATE FUNCTION dbo.tf_array2table
  22. (
  23. @array NVARCHAR(MAX), -- List of delimited values
  24. @delim NCHAR(1), -- Delimiter
  25. @remove_empty BIT -- Flag to remove empty values
  26. )
  27. -- Container for Array Items
  28. RETURNS @data TABLE(
  29. item_index INT IDENTITY(1,1), -- 1-based index of item
  30. item_value NVARCHAR(MAX), -- item value
  31. item_rank INT -- item rank if items are not unique
  32. )
  33. AS BEGIN
  34. -- Container for XML
  35. DECLARE
  36. @xml_text NVARCHAR(MAX),
  37. @xml XML;
  38. IF @remove_empty = 1 BEGIN
  39. -- Remove empty inner items
  40. WHILE (CHARINDEX(@delim + @delim, @array, 0) > 0) BEGIN
  41. SET @array = REPLACE(@array, @delim + @delim, @delim);
  42. END;
  43. -- Remove empty left item
  44. IF(LEFT(@array, 1) = @delim) BEGIN
  45. SET @array = SUBSTRING(@array, 2, LEN(@array)-1)
  46. END;
  47. -- Remove empty right item
  48. IF(RIGHT(@array, 1) = @delim) BEGIN
  49. SET @array = SUBSTRING(@array, 1, LEN(@array)-1)
  50. END;
  51. END;
  52. -- Prepare XML-Text
  53. SET @xml_text = N'<L><I>' +
  54. REPLACE(@array, @delim, N'</I><I>') +
  55. N'</I></L>';
  56. -- Convert Array to XML
  57. SET @xml = CAST(@xml_text AS XML);
  58. DECLARE @temp_data TABLE(
  59. item_index INT IDENTITY(1,1),
  60. item_value NVARCHAR(MAX)
  61. )
  62. -- Extract Array Items to Table-Variable
  63. INSERT INTO @temp_data(item_value)
  64. SELECT
  65. item_value = item.value('.', 'NVARCHAR(MAX)')
  66. FROM
  67. @xml.nodes('//I') XMLDATA(item);
  68. -- Calculate Rank for each item (to find non-unique items)
  69. INSERT INTO @data(item_value, item_rank)
  70. SELECT
  71. item_value,
  72. item_rank=RANK() OVER(PARTITION BY item_value ORDER BY item_index, item_value)
  73. FROM
  74. @temp_data
  75. ORDER BY
  76. item_index;
  77. RETURN;
  78. END;
  79. GO

Usages:


  1. -- Get all items
  2. SELECT item_index, item_value = IIF(item_value = '', '---', item_value), item_rank
  3. FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 0);
Result
item_indexitem_valueitem_rank
1---1
2ddd1
3bbb1
4ccc1
5---2
6---3
7ccc2
8ddd2
9eee1
10---4

  1. -- Get all non-empty items
  2. SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1);
Result
item_indexitem_valueitem_rank
1ddd1
2bbb1
3ccc1
4ccc2
5ddd2
6eee1

  1. -- Get all non-empty items and filter non-unique
  2. SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1)
  3. WHERE item_rank=1;
Result
item_indexitem_valueitem_rank
1ddd1
2bbb1
3ccc1
6eee1

No comments:

Post a Comment

Note: only a member of this blog may post a comment.