SQL Sting拆分为单列

对SQL很新,但我需要一些帮助,我确信这是一个简单的修复.

我在一个名为’Produce’的表中有一列数据,其中的水果类型存储在名为’Fruit’的列中.此列中的某些值以逗号分隔.

是否有一种简单的方法来拆分下面的结果,以便结果作为一列唯一条目？

例如.示例表

Fruit
-----
Apple
Plum
Pear,Mango
Pear

我希望返回的内容如下：

Fruit
-----
Apple
Plum
Pear
Mango

我曾尝试使用字符串拆分功能,但我想我已完全使用它.任何人都可以帮忙提供一些解释,如何做到这一点,好吗？如果有帮助,我正在使用T-SQL.

提前致谢.

解决方法

declare @table table (Fruit varchar(64))
insert into @table
values
('Apple'),('Plum'),('Pear,Mango'),('Pear')

select distinct
    Item
from
    @table
cross apply
    dbo.DelimitedSplit8K(Fruit,',')

或者,如果您使用的是SQL Server 2016 …

select distinct
    Item
from
    @table
cross apply
    string_split(Fruit,')

功能

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[DelimitedSplit8K] (@pString VARCHAR(8000),@pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!

RETURNS TABLE WITH SCHEMABINDING AS
RETURN

/* "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
enough to cover VARCHAR(8000)*/

  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),--10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a,E1 b),--10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a,E2 b),--10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),Item       = SUBSTRING(@pString,l.N1,l.L1)
   FROM cteLen l
;
GO

Jeff Moden Article for Function

（编辑：92站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!