CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduressql-server-2012
SiChiPan
SiChiPan

Reputation: 73

MSSQL 2012 Splitting Records Into Multiple Row

Currently my record are as below. One complete record are split by pipe | and data are separate by comma.

 123,870503-23-5370,021|456,830503-23-5371,031|789,870103-11-5372,041|654,870501-23-5373,051|321,880503-12-5374,061|987,870803-23-5375,071|109,870508-06-5376,081|174,810503-03-5377,091|509,870103-01-5378,101|687,870501-12-5379,131

How to split the record to become:

C1  C2             C3         
123 870503-23-5370 021
456 830503-23-5371 031
789 870103-11-5372 041
......

Upvotes: 0

Views: 60

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

This kind of operation should be done in application layer.

Quick workaround uisng XML to split based on |:

declare @S varchar(1000) = '123,870503-23-5370,021|456,830503-23-5371,031|789,870103-11-5372,041|654,870501-23-5373,051|321,880503-12-5374,061|987,870803-23-5375,071|109,870508-06-5376,081|174,810503-03-5377,091|509,870103-01-5378,101|687,870501-12-5379,131'

Spliting to each column using PARSENAME:

;WITH cte AS
(
select 
REPLACE(n.r.value('.', 'varchar(500)'), ',','.') As c
from (select cast('<r>'+replace(@S, '|', '</r><r>')+'</r>' as xml)) as s(XMLCol)
  cross apply s.XMLCol.nodes('r') as n(r)
)
SELECT PARSENAME(c,3) AS col1,
       PARSENAME(c,2) AS col2,
       PARSENAME(c,1) AS col3
FROM cte;

LiveDemo

Output:

╔══════╦════════════════╦══════╗
║ col1 ║      col2      ║ col3 ║
╠══════╬════════════════╬══════╣
║  123 ║ 870503-23-5370 ║  021 ║
║  456 ║ 830503-23-5371 ║  031 ║
║  789 ║ 870103-11-5372 ║  041 ║
║  654 ║ 870501-23-5373 ║  051 ║
║  321 ║ 880503-12-5374 ║  061 ║
║  987 ║ 870803-23-5375 ║  071 ║
║  109 ║ 870508-06-5376 ║  081 ║
║  174 ║ 810503-03-5377 ║  091 ║
║  509 ║ 870103-01-5378 ║  101 ║
║  687 ║ 870501-12-5379 ║  131 ║
╚══════╩════════════════╩══════╝

Warning:

Using XML may cause problems when your string contains some characters like <&'">.

PARSENAME is used to quicky split up to 4 slices. Works only with data that doesn't contain . character.

EDIT:

Inserting is very easy:

;WITH cte AS
(
select 
REPLACE(n.r.value('.', 'varchar(500)'), ',','.') As c
from (select cast('<r>'+replace(@S, '|', '</r><r>')+'</r>' as xml)) as s(XMLCol)
  cross apply s.XMLCol.nodes('r') as n(r)
)
INSERT INTO your_table(col1, col2, col3)
SELECT PARSENAME(c,3) AS col1,
       PARSENAME(c,2) AS col2,
       PARSENAME(c,1) AS col3
FROM cte;

Upvotes: 3

sheeni
sheeni

Reputation: 407

Another method could be simply export the data to a csv file (you can easily do it through the management studio) and then load that csv file using BULK INSERT by specifying field and record delimiters. Detailed explanation in how to do this can be found here: bulk insert csv

Upvotes: 1

Related Questions