CODEWITHSUNDEEP
mysqlsql
Krishna Kumar
Krishna Kumar

Reputation: 11

split a column into three separate columns with a delimiter

I have values in a column in the table For Eg: Abc-a,d,f,g,h;Acd-b,h,i,j;Asx-i,k,l,m

Now what i want is i need the values a,d,f,g,h to be copied into a separate column named 'Abc' similarly b,h,i,j to another column "Acd"(Adding clarity to the above point,i want all the comma separated values to come under a separate column and the column name will be the string which is prefixed before the '-').i should be splited using the delimiter ';'

Upvotes: 0

Views: 778

Answers (2)

Praloy Das
Praloy Das

Reputation: 325

DECLARE @DATA NVARCHAR(MAX), @COLUMNROWCOUNT INT, @COLUMNVALUE NVARCHAR(MAX)
DECLARE @VALUEROWCOUNT INT, @VALUE NVARCHAR(MAX), @SQLQUERY NVARCHAR(MAX)
SET @DATA='Abc-a,d,f,g,h;Acd-b,h,i,j;Asx-i,k,l,m'

CREATE TABLE #TEMPCOLUMNS(
    ID int IDENTITY(1,1) NOT NULL,
    VALUE NVARCHAR(MAX)
)
CREATE TABLE #TEMPVALUES(
    ID int IDENTITY(1,1) NOT NULL,
    VALUE NVARCHAR(MAX)
)
CREATE TABLE #TEMPCOLUMNVALUE(
    ID int IDENTITY(1,1) NOT NULL,
    COLUMNNAME NVARCHAR(MAX),
    COLUMNVALUE NVARCHAR(MAX)
)

INSERT INTO #TEMPCOLUMNS
SELECT value AS VALUE
FROM dbo.Split(@DATA, ';')

SET @COLUMNROWCOUNT=1
SET @COLUMNVALUE=''
WHILE @COLUMNROWCOUNT <=(SELECT COUNT(*) FROM #TEMPCOLUMNS)
BEGIN
    SET @COLUMNVALUE=(SELECT VALUE FROM #TEMPCOLUMNS WHERE ID=@COLUMNROWCOUNT)
    TRUNCATE TABLE #TEMPVALUES

    INSERT INTO #TEMPVALUES
    SELECT value AS VALUE
    FROM dbo.Split(@COLUMNVALUE, '-')

    INSERT INTO #TEMPCOLUMNVALUE SELECT
    (SELECT VALUE FROM #TEMPVALUES WHERE ID=1) COLUMNNAME,
    (SELECT VALUE FROM #TEMPVALUES WHERE ID=2) COLUMNVALUE  

    SET @COLUMNROWCOUNT=@COLUMNROWCOUNT+1
END

SET @VALUEROWCOUNT=1
SET @VALUE=''
SET @SQLQUERY='CREATE TABLE #TEMP( '
WHILE @VALUEROWCOUNT <=(SELECT COUNT(*) FROM #TEMPCOLUMNVALUE)
BEGIN
    SET @VALUE=(SELECT COLUMNNAME FROM #TEMPCOLUMNVALUE WHERE ID=@VALUEROWCOUNT)
    SET @SQLQUERY=@SQLQUERY+@VALUE+' NVARCHAR(MAX)'
    IF (@VALUEROWCOUNT <>(SELECT COUNT(*) FROM #TEMPCOLUMNVALUE))
    BEGIN
        SET @SQLQUERY=@SQLQUERY+','
    END
    SET @VALUEROWCOUNT=@VALUEROWCOUNT+1
END
SET @SQLQUERY=@SQLQUERY+')'

SET @VALUEROWCOUNT=1
SET @VALUE=''
SET @SQLQUERY=@SQLQUERY+'INSERT INTO #TEMP SELECT '
WHILE @VALUEROWCOUNT <=(SELECT COUNT(*) FROM #TEMPCOLUMNVALUE)
BEGIN
    SET @VALUE=(SELECT COLUMNVALUE FROM #TEMPCOLUMNVALUE WHERE ID=@VALUEROWCOUNT)
    SET @SQLQUERY=@SQLQUERY+''''+@VALUE+''''
    IF (@VALUEROWCOUNT <>(SELECT COUNT(*) FROM #TEMPCOLUMNVALUE))
    BEGIN
        SET @SQLQUERY=@SQLQUERY+','
    END
    SET @VALUEROWCOUNT=@VALUEROWCOUNT+1
END
SET @SQLQUERY=@SQLQUERY+'SELECT * FROM #TEMP DROP TABLE #TEMP'

PRINT(@SQLQUERY)
EXEC(@SQLQUERY)

DROP TABLE #TEMPCOLUMNS
DROP TABLE #TEMPVALUES
DROP TABLE #TEMPCOLUMNVALUE

SQL FUNCTION:

CREATE FUNCTION [dbo].[Split]    
 (    
  @List nvarchar(2000),    
  @SplitOn nvarchar(5)    
 )      
 RETURNS @RtnValue table     
 (    

  Id int identity(1,1),    
  Value nvarchar(100)    
 )     
 AS      
 BEGIN     
  While (Charindex(@SplitOn,@List)>0)    
  Begin    

   Insert Into @RtnValue (value)    
   Select     
    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))    

   Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))    
  End    

  Insert Into @RtnValue (Value)    
  Select Value = ltrim(rtrim(@List))    

  Return    
 END

Please try it.

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

First of all never store data as comma separated values, its a bad practice and you should always normalize the data

Now as far as your current situation is concern and if the pattern is same you can extract data as below using the substring_index() function 

mysql> select substring_index(substring_index('Abc-a,d,f,g,h;Acd-b,h,i,j;Asx-i,k,l,m',';',1),'Abc-',-1) as Abc ;
+-----------+
| Abc       |
+-----------+
| a,d,f,g,h |
+-----------+

mysql> select substring_index(substring_index('Abc-a,d,f,g,h;Acd-b,h,i,j;Asx-i,k,l,m',';',2),'Acd-',-1) as Acd;
+---------+
| Acd     |
+---------+
| b,h,i,j |
+---------+

mysql> select substring_index(substring_index('Abc-a,d,f,g,h;Acd-b,h,i,j;Asx-i,k,l,m',';',-1),'Asx-',-1) as Asx;
+---------+
| Asx     |
+---------+
| i,k,l,m |
+---------+

Finally putting all together you can have the update command as

update your_table
set
Abc = substring_index(substring_index('Abc-a,d,f,g,h;Acd-b,h,i,j;Asx-i,k,l,m',';',1),'Abc-',-1),
Acd = substring_index(substring_index('Abc-a,d,f,g,h;Acd-b,h,i,j;Asx-i,k,l,m',';',2),'Acd-',-1),
Asx = substring_index(substring_index('Abc-a,d,f,g,h;Acd-b,h,i,j;Asx-i,k,l,m',';',-1),'Asx-',-1) ;

Note that I have added the complete string in the above example, you may just add the column name where the values are stored

Upvotes: 1

Related Questions