String split in SQL Server-CodePudding

I have a question about SQL Server: how to split a string using _ and get required format.

If we do not have - symbol then keep empty value.

Table :

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')

Based on this data, I want output like this:

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

I have tried with SQL like this:

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

This query however is not returning the expected results.

How can I write a query to achieve this task in SQL Server?

CodePudding user response：

Please try the following solution.

It will work starting from SQL Server 2016 onwards.

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;

Output

 ----- ---------------------------------- ------ ------------------- 
| 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 |
 ----- ---------------------------------- ------ -------------------

CodePudding user response：

use this query:

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

CodePudding user response：

An APPLY, with two STRING_SPLIT and STRING_AGG, will work in MS Sql Server 2017 and onwards.

SELECT s.sid, ca.course, s.name
FROM student s
CROSS APPLY(
  SELECT STRING_AGG(spl2.value, ',') AS course
  FROM STRING_SPLIT(s.course,';') spl1
  CROSS APPLY STRING_SPLIT(SUBSTRING(spl1.value, CHARINDEX('-',spl1.value '-') 1, LEN(spl1.value)),'-') spl2
) ca;

sid	course	name
1	sql,Java	abc
2	net,oracle,python	xyz
3		axy
4	python,java,mysql	anr