sql server help

Question

Hi I have one doubt in sql server .how to split string using _ and get required format in sql server .
if we donot have - symbole then keep empty value.

Table :

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

based on above data I want out put like below :
Sid | course |name
1 |sql,java |abc
2 |net,oracle,python |xyz
3 | |axy
4 |python,java,mysql |anr

I have tried like below :

select sid,substring([course],charindex([course],'-')+1,len([course]))course,name from student.
above query not given expected result.

can you please tell me how to write a query to achive this task in sql server

Answer 1

SQL Server 2017 and up:

DECLARE @student table(
[sid] [int] NULL,
[course] [varchar](500) NULL,
[name] [varchar](50) NULL
)
INSERT @student ([sid], [course], [name]) VALUES (1, N'database-sql;FE-Java', N'abc')
INSERT @student ([sid], [course], [name]) VALUES (2, N'FE-net;database-oracle;FE-python', N'xyz')
INSERT @student ([sid], [course], [name]) VALUES (3, N'test', N'axy')
INSERT @student ([sid], [course], [name]) VALUES (4, N'FE-python-java;base-mysql', N'anr')

;WITH cteSource
AS (
    SELECT  *,
        CAST('<v><i>' + REPLACE(REPLACE(course, ';', '</i></v><v><i>'), '-', '</i><i>') + '</i></v>' AS XML) AS Data
    FROM    @student
),
cteSplit  AS (SELECT    s.*,
        ISNULL(v.value('i[2]', 'varchar(100)'),
        '') AS [splitted]

FROM        cteSource AS s
CROSS APPLY Data.nodes('v') AS n(v))

SELECT sID, course, name, STRING_AGG(splitted, ', ') AS courses FROM cteSplit
GROUP BY sID, course, name

Answer 2

Alternative solution without using STRING_AGG function:

DECLARE @student table(
[sid] [int] NULL,
[course] [varchar](500) NULL,
[name] [varchar](50) NULL
)
INSERT @student ([sid], [course], [name]) VALUES (1, N'database-sql;FE-Java', N'abc')
INSERT @student ([sid], [course], [name]) VALUES (2, N'FE-net;database-oracle;FE-python', N'xyz')
INSERT @student ([sid], [course], [name]) VALUES (3, N'test', N'axy')
INSERT @student ([sid], [course], [name]) VALUES (4, N'FE-python-java;base-mysql', N'anr')

;WITH cteSource
AS (
    SELECT  *,
        CAST('<v><i>' + REPLACE(REPLACE(course, ';', '</i></v><v><i>'), '-', '</i><i>') + '</i></v>' AS XML) AS Data
    FROM    @student
),
cteSplit  AS (SELECT    s.*,
        ISNULL(v.value('i[2]', 'varchar(100)'),
        '') AS [splitted]

FROM        cteSource AS s
CROSS APPLY Data.nodes('v') AS n(v))

SELECT sID,  name, 
STUFF((SELECT ',' + splitted FROM cteSplit
  s2
where s2.sID = cteSplit.sID FOR XML PATH('')),1,1,'') AS Course
FROM cteSplit
GROUP BY SID, name

Answer 3

Hi @harinathu ,

Please try the following solutions.

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) = '-';  
  
-- Method #1  
-- SQL Server 2016 onwards  
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;  
  
-- Method #2  
-- SQL Server 2012 onwards  
;WITH rs  AS   
(  
	SELECT s.*,  
		-- get all tokens starting from the 2nd  
		ISNULL(v.query('data(i[position() ge 2])').value('.', 'varchar(100)'), '') AS [splitted]  
		FROM @tbl AS s  
		CROSS APPLY (SELECT TRY_CAST('<v><i>' +   
			REPLACE(REPLACE(course, @separator, '</i></v><v><i>'), @dash, '</i><i>') +   
			'</i></v>' AS XML)) AS t(xml_data)  
		CROSS APPLY xml_data.nodes('/v') AS n(v)  
)  
SELECT sID, course, name  
	, STUFF((SELECT @comma + REPLACE(splitted,SPACE(1),@comma)  
FROM rs  
where t.sID = rs.sID FOR XML PATH('')),1,1,'') AS Course  
FROM @tbl AS t  
GROUP BY SID, course, name;  

Output

+-----+----------------------------------+------+-------------------+  
| sID |              course              | name |      Course       |  
+-----+----------------------------------+------+-------------------+  
|   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 4

Hi @harinathu
If SQL Server 2016 (13.x) and later,you can have a try on STRING_SPLIT function.Check this query:

;WITH CTE AS  
(  
SELECT sid,name,CASE WHEN CHARINDEX('-',VALUE)>0   
                     THEN SUBSTRING( VALUE,CHARINDEX('-',VALUE)+1,len(VALUE))  ELSE '' END AS Course  
FROM student CROSS APPLY STRING_SPLIT(course,';')  
)  
SELECT DISTINCT sid,STUFF((SELECT ',' + REPLACE(Course,'-',',')FROM CTE WHERE sid = C.sid   
						   FOR XML PATH('')),1,1,'')AS Course,name  
FROM CTE C   
GROUP BY sid, course, name;  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.