SQL Server 中的字符串分割

Question

我有一个关于 SQL Server 的问题：如何使用 _ 分割字符串并获取所需的格式。

如果我们没有 - 符号，则保留空值。

表：

CREATE TABLE [dbo].[student]
(
    [sid] [int] NULL,
    [course] [varchar](500) NULL,
    [name] [varchar](50) NULL
) 

INSERT INTO [dbo].[student] ([sid], [course], [name]) 
VALUES (1, N'database-sql;FE-Java', N'abc')
INSERT INTO [dbo].[student] ([sid], [course], [name]) 
VALUES (2, N'FE-net;database-oracle;FE-python', N'xyz')
INSERT INTO [dbo].[student] ([sid], [course], [name]) 
VALUES (3, N'test', N'axy')
INSERT INTO [dbo].[student] ([sid], [course], [name]) 
VALUES (4, N'FE-python-java;base-mysql', N'anr')

基于这些数据，我想要这样的输出：

Sid | course            |name 
----+-------------------+-----
1   |sql,java           |abc
2   |net,oracle,python  |xyz
3   |                   |axy
4   |python,java,mysql  |anr

我已经尝试使用这样的 SQL：

select 
    sid,
    substring([course], charindex([course], '-') + 1, len([course])) course,
    name 
from 
    student

但是，此查询没有返回预期的结果。

如何在 SQL Server 中编写查询来完成此任务？

Answer 1

请尝试以下解决方案。

它将从 SQL Server 2016 开始运行。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (sid int NULL, course varchar(500) NULL, name varchar(50) NULL); 
INSERT INTO @tbl (sid, course, name) VALUES 
(1, N'database-sql;FE-Java', N'abc'),
(2, N'FE-net;database-oracle;FE-python', N'xyz'),
(3, N'test', N'axy'),
(4, N'FE-python-java;base-mysql', N'anr');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ';'
    , @comma CHAR(1) = ','
    , @dash CHAR(1) = '-';

SELECT tbl.*
    , REPLACE(STUFF((SELECT @comma + IIF(pos=0,'',SUBSTRING(value, pos + 1, LEN(value)))
            FROM @tbl AS tbl_inner
                CROSS APPLY STRING_SPLIT(tbl_inner.course, @separator) AS ss
                CROSS APPLY (SELECT CHARINDEX(@dash, value)) AS t(pos)
            WHERE tbl_inner.sid = tbl.sid
            FOR XML PATH('')), 1, 1, ''),@dash,@comma) AS Result
FROM @tbl AS tbl; 

输出

+-----+----------------------------------+------+-------------------+
| sid |              course              | name |      Result       |
+-----+----------------------------------+------+-------------------+
|   1 | database-sql;FE-Java             | abc  | sql,Java          |
|   2 | FE-net;database-oracle;FE-python | xyz  | net,oracle,python |
|   3 | test                             | axy  |                   |
|   4 | FE-python-java;base-mysql        | anr  | python,java,mysql |
+-----+----------------------------------+------+-------------------+

Answer 2

使用这个查询：

SELECT 
   sid,
   course,
   name,
   STRING_AGG(CA2.C,',') as result 
FROM student AS s
CROSS APPLY(
   SELECT * FROM
   string_split(s.course,';')
)AS CA1
CROSS APPLY(
   SELECT SUBSTRING(CA1.value, charindex('-',CA1.value) + 1, len(CA1.value)) c 
) AS CA2 
GROUP BY     
   sid,
   course,
   name

Answer 3

ALTER FUNCTION [dbo].[Split]    
(     
    @String VARCHAR(max),     
    @Delimiter VARCHAR(5)     
)     
RETURNS @SplittedValues TABLE     
(     
   OccurenceId SMALLINT IDENTITY(1,1),     
   SplitValue VARCHAR(max)     
)     
AS     
BEGIN     
    DECLARE @SplitLength INT     
        
    WHILE LEN(@String) > 0     
    BEGIN     
        SELECT @SplitLength = (CASE CHARINDEX(@Delimiter,@String) WHEN 0 THEN     
            LEN(@String) 
        ELSE 
            CHARINDEX(@Delimiter,@String) -1  END)     
        INSERT INTO @SplittedValues     
        SELECT SUBSTRING(@String,1,@SplitLength)     
        SELECT @String = (CASE LTRIM(RTRIM(LEN(@String) - @SplitLength)) WHEN 0 THEN 
            ''     
        ELSE 
            RIGHT(@String, LTRIM(RTRIM(LEN(@String) - @SplitLength - 1))) END)     
        END     
    RETURN     
END