sql server help

harinathu 6 Reputation points
2022-03-18T17:25:08.127+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,859 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,561 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Naomi 7,361 Reputation points
    2022-03-18T18:09:18.01+00:00

    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
    

  2. Naomi 7,361 Reputation points
    2022-03-18T19:10:07.927+00:00

    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
    

  3. Yitzhak Khabinsky 25,201 Reputation points
    2022-03-21T01:14:21.537+00:00

    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 |  
    +-----+----------------------------------+------+-------------------+  
    
    0 comments No comments

  4. LiHong-MSFT 10,046 Reputation points
    2022-03-21T05:44:12.59+00:00

    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:
    185072-image.png

    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.

    0 comments No comments