TSQL/SQL Server — табличная функция для анализа/разделения строки с разделителями на несколько/отдельные столбцы

Итак, мой первый пост - это не столько вопрос, сколько утверждение! Прости.

Мне нужно было преобразовать строки с разделителями, хранящиеся в столбцах таблицы VarChar, в несколько/отдельных столбцов для одной и той же записи. (Это программное обеспечение COTS, так что, пожалуйста, не беспокойтесь о том, почему таблица спроектирована неправильно.) После поиска в Интернете до тошноты того, как создать общий однострочный вызов, чтобы сделать это - и найти много того, как этого не делать - Я создал свой собственный. (Название не является настоящим творческим.)

Возвраты: таблица с последовательно пронумерованными/именованными столбцами, начинающимися с [Col1]. Если входное значение не указано, возвращается пустая строка. Если предоставлено менее 32 значений, все значения после последнего возвращаются как нулевые. Если указано более 32 значений, они игнорируются.

Предварительные требования: Таблица чисел/счетчиков (к счастью, наша база данных уже содержала 'dbo.numbers').

Допущения: не более 32 значений с разделителями. (Если вам нужно больше, измените «WHERE tNumbers.Number BETWEEN 1 AND XXX» и добавьте больше предварительно именованных столбцов «, [Col33]..., [ColXXX]».)

Проблемы: самый первый столбец всегда заполняется, даже если @InputString имеет значение NULL.

--======================================================================
--SMOZISEK 2017/09 CREATED
--======================================================================
CREATE FUNCTION dbo.fStringToPivotTable 
        (@InputString   VARCHAR(8000)
        ,@Delimiter     VARCHAR(30)         =   ','
        )
    RETURNS TABLE AS RETURN
    WITH    cteElements AS  (
        SELECT  ElementNumber       =   ROW_NUMBER() OVER(PARTITION BY @InputString ORDER BY (SELECT 0))
                ,ElementValue       =   NodeList.NodeElement.value('.','VARCHAR(1022)')
        FROM        (SELECT TRY_CONVERT(XML,CONCAT('<X>',REPLACE(@InputString,@Delimiter,'</X><X>'),'</X>')) AS InputXML)   AS InputTable
        CROSS APPLY InputTable.InputXML.nodes('/X')                                                                         AS NodeList(NodeElement)
    )
    SELECT  PivotTable.*
        FROM    (
            SELECT  ColumnName          =   CONCAT('Col',tNumbers.Number)
                    ,ColumnValue        =   tElements.ElementValue
            FROM        DBO.NUMBERS         AS  tNumbers                --DEPENDENT ON ANY EXISTING NUMBER/TALLY TABLE!!!
            LEFT JOIN   cteElements         AS  tElements
                ON      tNumbers.Number     =   tElements.ElementNumber
            WHERE       tNumbers.Number     BETWEEN 1 AND 32
        )   AS  XmlSource
    PIVOT (
        MAX(ColumnValue)
        FOR ColumnName
        IN  ([Col1] ,[Col2] ,[Col3] ,[Col4] ,[Col5] ,[Col6] ,[Col7] ,[Col8]
            ,[Col9] ,[Col10],[Col11],[Col12],[Col13],[Col14],[Col15],[Col16]
            ,[Col17],[Col18],[Col19],[Col20],[Col21],[Col22],[Col23],[Col24]
            ,[Col25],[Col26],[Col27],[Col28],[Col29],[Col30],[Col31],[Col32]
            )
    )   AS  PivotTable
    ;
    GO

Контрольная работа:

SELECT  * 
FROM    dbo.fStringToPivotTable ('|Height|Weight||Length|Width||Color|Shade||Up|Down||Top|Bottom||Red|Blue|','|')   ;

Использование:

SELECT  1       AS ID,'Title^FirstName^MiddleName^LastName^Suffix' AS Name
INTO    #TempTable
UNION SELECT    2,'Mr.^Scott^A.^Mozisek^Sr.'
UNION SELECT    3,'Ms.^Jane^Q.^Doe^'
UNION SELECT    5,NULL
UNION SELECT    7,'^Betsy^^Ross^'
;

SELECT  SourceTable.*
        ,ChildTable.Col1        AS  ColTitle
        ,ChildTable.Col2        AS  ColFirst
        ,ChildTable.Col3        AS  ColMiddle
        ,ChildTable.Col4        AS  ColLast
        ,ChildTable.Col5        AS  ColSuffix
FROM    #TempTable              AS  SourceTable
OUTER APPLY dbo.fStringToPivotTable(SourceTable.Name,'^')       AS  ChildTable
;

Нет, я не проверял никаких планов (мне просто нужно было, чтобы они работали). О, да: SQL Server 2012 (12.0 SP2)

Комментарии? Исправления? Улучшения?


parsing sql-server tsql delimited-text
person RedMartian    schedule 07.09.2017    source источник
comment
В чем именно заключается ваш вопрос?   -  person Maciej Jureczko    schedule 07.09.2017

Ответы (1)


arrow_upward
0
arrow_downward

Вот мой ТВФ. Легко расширить до 32 (схема довольно четкая).

Это прямой XML без затрат на PIVOT.

Пример. Обратите внимание на OUTER APPLY --- используйте CROSS APPLY для исключения значений NULL

Select A.ID
      ,B.*
 From #TempTable A
 Outer Apply [dbo].[tvf-Str-Parse-Row](A.Name,'^') B

Возврат

введите здесь описание изображения

Пользовательская функция, если интересно

CREATE FUNCTION [dbo].[tvf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (
    Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
          ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
          ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
          ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
          ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
          ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
          ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
          ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
          ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
    From  (Select Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
    Where @String is not null
)
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[tvf-Str-Parse-Row]('Dog,Cat,House,Car',',')
--Select * from [dbo].[tvf-Str-Parse-Row]('John <test> Cappelletti',' ')
person John Cappelletti    schedule 07.09.2017