Question

这从https://stackoverflow.com/a/878964/482595。

CREATE FUNCTION uf_Split
( 
    @Text NVARCHAR(MAX), 
    @Delimiter CHAR(1),
    @Quote CHAR(1)
) 
RETURNS @Result TABLE 
( 
    [Index] INT NOT NULL IDENTITY(1, 1), 
    [Value] NVARCHAR(4000) NULL,
    [CharPos] INT
)
AS 
BEGIN 
    DECLARE @start BIGINT; SET @start = 1
    DECLARE @end BIGINT; SET @end = 1

    IF @Text is null
    BEGIN
      RETURN
    END 

    WHILE 1=1 
    BEGIN 
        SET @end = 
            CASE
                WHEN CHARINDEX(@Quote, @Text, @start) = @start THEN CHARINDEX(@Quote + @Delimiter, @Text, @start + 1)
                ELSE CHARINDEX(@Delimiter, @Text, @start)
            END

        IF ISNULL(@end, 0) = 0 
        BEGIN 
            -- Delimiter could not be found in the remainder of the text:
            INSERT @Result([Value], [CharPos]) VALUES(SUBSTRING(@Text, @start, DATALENGTH(@Text)), @start)
            BREAK 
        END 
        ELSE IF (CHARINDEX(@Quote, @Text, @start) = @start) AND (CHARINDEX(@Quote + @Delimiter, @Text, @start + 1) = @end)
        BEGIN
            INSERT @Result([Value], [CharPos]) VALUES(SUBSTRING(@Text, @start + 1, @end - @start - 1), @start)
            SET @start = @end + 2
        END
        ELSE 
        BEGIN
            INSERT @Result([Value], [CharPos]) VALUES(SUBSTRING(@Text, @start, @end - @start), @start)
            SET @start = @end + 1
        END
    END 

    RETURN
END
GO

Answer 1

我用 t- sql 中的两阶段分割法拍摄了一张照片。我当然有兴趣看看其他人如何接近这一段。如果这些字符串很大, 或者您想要处理大排集, 我将查看其他选项, 也许是 BULK INSERT 或 CLR 。

declare @data nvarchar(1000) =  abc,def,"ghi,jkl",mno,"yak","yak,123" ;


declare @x xml;
select  @x = cast( <d>  + replace(@data,  " ,  </d><d> ) +  </d>  as xml);

;with c(d,i)
as  (   select  p.n.value( . ,  nvarchar(max) ) AS data,
                case
                    when left(p.n.value( . ,  nvarchar(max) ), 1) =  ,  then 1
                    when right(p.n.value( . ,  nvarchar(max) ), 1) =  ,  then 1 
                    else 0 
                end
        from    @x.nodes( /d ) p(n)
    )       
select  d
from    c
where   i = 0 and len(d) > 0
union all
select  p.n.value( . ,  nvarchar(max) )
from    (   select cast( <d>  + replace(d,  , ,  </d><d> ) +  </d>  as xml)
            from c
            where   i=1
        ) d(x)
cross
apply   d.x.nodes( /d )p(n)
where   len(p.n.value( . ,  nvarchar(max) )) > 0;

Answer 2

这样做的最佳方法来定义您函数中嵌入逗号的特例, 当您在字符串起始时分割嵌入逗号的检查并删除该子字符串时。

Answer 3

伟大的解决方案, 感谢共享! 我确实做了一个修改以适应我的情况。一些数据嵌入了 < code_ amp; , 导致一个非法字符错误。为了克服这个问题, 我使用 REPLACE 函数将它从 < code_ amp; 更改为 < code_ amp; amp; , 这样它就可以在 XML 中进行解析。稍后, 当我需要修改它时, 我用 < code_ amp; amp; 替换了数值, 并有 < code_ amp; < / code > 。我肯定有更好的方法来解决这个问题, 但肯定解决了我们的问题。下面是您对我的更改进行代码抽样。

DECLARE @data NVARCHAR(MAX) =  abc,def,"ghi,jkl",mno,"yak","yak,123","zzzz & yyyy" ;

SELECT @data = REPLACE(REPLACE(REPLACE(@data,  & ,  &amp; ),  < ,  &lt; ),  > ,  &gt; ); /**** Replace characters (&, <, >) ****/

DECLARE @x XML = CAST( <d>  + REPLACE(@data,  " ,  </d><d> ) +  </d>  AS XML);

;WITH c (d,i) AS
    (
        SELECT  p.n.value( . ,  NVARCHAR(MAX) ) AS DATA,
                CASE
                    WHEN LEFT(p.n.value( . ,  NVARCHAR(MAX) ), 1) =  , 
                        THEN 1
                    WHEN RIGHT(p.n.value( . ,  NVARCHAR(MAX) ), 1) =  , 
                        THEN 1 
                    ELSE 0 
                END
        FROM @x.nodes( /d ) p(n)
    )

SELECT REPLACE(REPLACE(REPLACE([z].[d],  &amp; ,  & ),  &lt; ,  < ),  &gt; ,  > ) AS d /**** Restore characters (&, <, >) ****/
FROM (
        SELECT d
        FROM c
        WHERE i = 0
            AND LEN(d) > 0

        UNION ALL

        SELECT p.n.value( . ,  NVARCHAR(MAX) )
        FROM (
                SELECT cast( <d>  + replace(d,  , ,  </d><d> ) +  </d>  AS XML)
                FROM c
                WHERE i = 1
            ) d(x)
        CROSS APPLY d.x.nodes( /d )p(n)
        WHERE LEN(p.n.value( . ,  NVARCHAR(MAX) )) > 0
    ) AS z
ORDER BY d;

友情链接