• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

need to split

I have column of varchar(5000) which stores the data in below format

;AAA; ;BBBBBBB; ;CCCCCC;

Now i want to split the data and put into different columns in different table.
col 1 col2 col3
AAA  BBBBBBB CCCCCC
0
VIVEKANANDHAN_PERIASAMY
Asked:
VIVEKANANDHAN_PERIASAMY
  • 3
1 Solution
 
Valliappan ANSenior Tech ConsultantCommented:
0
 
Valliappan ANSenior Tech ConsultantCommented:
If you want dynamic split check this link (try the solution in the end, on dynamic shredding):

http://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-Split-a-308206f3

hth.
0
 
Valliappan ANSenior Tech ConsultantCommented:
Hi,

You may split the string, as given in this link -  on dynamic shredding at the bottom of the page, and then dynamically create the columns.

http://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-Split-a-308206f3

Using the above link code reference, have created a sample script to do the same for e.g.

--Using XML method  

--DROP TABLE ##FormSplitXML
 
;With FormSplitXML (id, name, X)
AS 
( 
  SELECT 10 AS Id, 'test' AS Name, 
    CONVERT(XML,'<r><n>'   
    + REPLACE('AAAAA;BB;CCCC',';', '</n><n>') + '</n></r>') AS X 
) 
 
SELECT * INTO ##FormSplitXML FROM FormSplitXML 
 
 
---Using XML method -  Manual shredding 
 
--SELECT  Id,  
-- i.value('n[1]','varchar(100)') AS Name, 
-- i.value('n[2]','varchar(100)') AS Surname 
-- FROM ##FormSplitXML Spt 
-- CROSS APPLY Spt.X.nodes('/r') x(i) 
 
--Using XML method - Dynamic shredding 
 
SELECT Id, 
       i.value('local-name(.)','varchar(100)') ColumnName, 
       i.value('.','varchar(100)') ColumnValue 
       INTO ##temp 
FROM ##FormSplitXML Spt 
CROSS APPLY Spt.X.nodes('//*[text()]') x(i) 
 
 
DECLARE @SQL NVARCHAR(MAX), 
        @i INT, 
        @MaxCount INT 
 
SELECT @MaxCount = MAX(cnt) 
FROM ( 
    SELECT Id 
        ,COUNT(ColumnValue) AS cnt 
    FROM ##temp 
    GROUP BY Id 
    ) X; 
  
SET @i = 0; 
  
WHILE @i < @MaxCount 
BEGIN 
    SET @i = @i + 1; 
    SET @SQL = COALESCE(@Sql + ', ', '') + 'Name' + cast(@i AS NVARCHAR(10)); 
END 
  
SET @SQL = N';WITH CTE AS ( 
   SELECT Id, ColumnValue, ''Name''  
   + CAST(row_number() OVER (PARTITION BY ID ORDER BY Id DESC) AS Varchar(10)) AS RowNo 
   FROM   ##temp) 
SELECT * 
FROM   CTE 
PIVOT (MAX(ColumnValue) FOR RowNo IN (' + @SQL + N')) pvt'; 
  
--PRINT @SQL; 
  
EXECUTE (@SQL); 
 
DROP TABLE ##temp 
DROP TABLE ##FormSplitXML

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now