sql server help

Question

sql server help

harinathu 6

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

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

Yitzhak Khabinsky 26,486 Reputation points

2022-03-18T18:33:55.6+00:00

@harinathu ,

Your question was already answered here: https://stackoverflow.com/questions/71530958/string-split-help-in-sql-server/71531378#71531378
Yitzhak Khabinsky 26,486 Reputation points

2022-03-22T15:19:10.847+00:00

@harinathu ,

What's the latest?
Did you have a chance to try the proposed solutions?

Please don't forget to click "Accept Answer" the response(s) that helped you to resolve the issue. This can be beneficial to other community members reading this thread.

4 answers

Your answer

Yitzhak Khabinsky 26,486 Reputation points

2022-03-18T18:33:55.6+00:00

@harinathu ,

Your question was already answered here: https://stackoverflow.com/questions/71530958/string-split-help-in-sql-server/71531378#71531378
Yitzhak Khabinsky 26,486 Reputation points

2022-03-22T15:19:10.847+00:00

@harinathu ,

What's the latest?
Did you have a chance to try the proposed solutions?

Please don't forget to click "Accept Answer" the response(s) that helped you to resolve the issue. This can be beneficial to other community members reading this thread.

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

harinathu 6 Reputation points

2022-03-18T18:54:28.403+00:00

getting error : Msg 195, Level 15, State 10, Line 14
'STRING_AGG' is not a recognized built-in function name.

Completion time: 2022-03-19T00:22:43.8999535+05:30 .can you please tell me any ways to achieve this task.

Answer 2

Naomi Nosonovsky 8,331

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

Yitzhak Khabinsky 26,486 Reputation points

2022-03-18T19:18:39.51+00:00

@Anonymous ,

You need to adjust the answer to handle the sid=4 row scenario.
Naomi Nosonovsky 8,331 Reputation points

2022-03-18T20:12:05.137+00:00

I see - it's trickier than I thought.

Yitzhak Khabinsky 26,486

@Anonymous ,

It is relatively simple to resolve.

Check it out below. Lines #12 and 16.

;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.*,  
		-- get all tokens starting from the 2nd  
		ISNULL(v.query('data(i[position() ge 2])').value('.', 'varchar(100)'), '') AS [splitted]  
	 FROM cteSource AS s  
		CROSS APPLY Data.nodes('/v') AS n(v)  
 )  
 SELECT sID, course, name, STRING_AGG(REPLACE(splitted,SPACE(1),', '), ', ') AS courses   
 FROM cteSplit  
 GROUP BY sID, course, name;

Ronen Ariely 15,201 Reputation points

2022-03-18T22:26:13.877+00:00

You just need to use the version without STRING_AGG and you solve it :-)

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.

Share via

sql server help

4 answers

Your answer