MS SQL Split a column value using delimiters and update in other field

Question

Ex, I have a table like this,

ID Name
1  Apple,banana
2  Grape,Orange
3  Papaya,Jackfruit

I need to split (,) and save like this in SQL

ID Name    Name2
1  Apple   banana
2  Grape   Orange
3  Papaya  Jackfruit

Answer 1

The fastest, most scaleable way to split strings before SQL Server 2016 is to write a SQLCLR method that splits strings, like this one. SQL Server 2016 introduced the STRING_SPLIT function which is even faster.

The second fastest way to split strings for versions before SQL Server 2016 is to convert the separated text into XML and use XML operators to retrieve the individual items. The typical usage, as shown in Aaron Bertrand's articles returns items as rows. It can be adapted easily to return items as columns:

declare @table table (ID int, Name nvarchar(200))
insert into @table
values
(1,'Apple,banana'),
(2,'Grape,Orange'),
(3,'Papaya,Jackfruit');


with items as (
    select 
        ID,
        xmlField= cast('<item><tag>'                            
                        + replace(Name,',','</tag><tag>')
                        + '</tag></item>' as xml) 
    from @table
)
-- Step 2: Select different tags and display them as fields
select 
    y.item.value('(tag/text())[1]','nvarchar(20)') As Name1,
    y.item.value('(tag/text())[2]','nvarchar(20)') as Name2
from items outer apply xmlField.nodes('item') as y(item)

This returns :

1   Apple   banana
2   Grape   Orange
3   Papaya  Jackfruit

This works by first converting Name1,Name2 to <item><tag>Name1</tag><tag>Name2</tag><item> which can be cast to XML and returned as xmlField.

outer apply xmlField.nodes('item') as y(item) converts this field to a table of items named y. Only one item row exists in each field.

Finally, y.item.value('(tag/text())[1]','nvarchar(20)') extracts the text of the first tag element as Name1.

This can be extended easily to multiple entries, or to return entries as different elements.

The number of columns has to be known in advance. SQL, the language, doesn't allow an arbitrary number of columns. If different fields contain a different number of tokens, they'll have to be returned as rows.

In this case, you should use STRING_SPLIT if you target SQL Server 2016 or the original version of the XML splitting technique :

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       nvarchar(max),
   @Delimiter  nvarchar(10)
)
RETURNS TABLE WITH SCHEMABINDING
AS
   RETURN (SELECT [value] = y.i.value('(./text())[1]', 'varchar(8000)')
      FROM (SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i));

It's worth checking Performance Surprises and Assumptions : STRING_SPLIT() which compares all available string splitting techniques to find the fastest and most scaleable

Answer 2

We get the Result by using Lead and Lag Function along with Split String And insert the Result set in to your table as You Required

IF OBJECT_ID('tempdb..#InsertTable') IS NOT NULL
DROP TABLE #InsertTable
DECLARE @table TABLE (ID INT, Name VARCHAR(50))

CREATE TABLE #InsertTable (ID INT,Name1 VARCHAR(100),Name2 VARCHAR(100))

INSERT INTO @table
SELECT 1,'Apple,Banana'  UNION ALL
SELECT 2,'Grape,Orange'  UNION ALL
SELECT 3,'Papaya,Jackfruit'

INSERT INTO #InsertTable(ID,Name1,Name2)
SELECT  DISTINCT ID,
        ISNULL(Name1,LagName1) AS Name1 ,
        ISNULL(Name2,LeadName2) AS Name2 

FROM
(
SELECT ID,
Name1,
LAG(NAme1,1)OVER(ORDER BY ID) LagName1,
Name2,
LEAD(Name2,1)OVER(ORDER BY ID)LeadName2
FROM
(
SELECT ID,   CASE WHEN Seq%2=1 THEN Name END AS Name1,
             CASE WHEN Seq%2=0 THEN Name END AS Name2  
 FROM
(
SELECT Row_NUmber ()OVER(ORDER BY ID )AS Seq,ID, Split.a.value('.', 'VARCHAR(1000)') AS Name
            FROM  (
                SELECT ID,  CAST('<S>' + REPLACE(Name, ',', '</S><S>') + '</S>' AS XML) AS Name
                FROM @table
                ) AS A
            CROSS APPLY Name.nodes('/S') AS Split(a) 

)Dt
)DT2
)Final


SELECT * FROM #InsertTable

Result

ID  Name1   Name2
----------------------
1   Apple   Banana
2   Grape   Orange
3   Papaya  Jackfruit