Separating records in same Database column

Question

Sorry about the title but the question is hard to sumarize. I am dealing with a poorly constructed database and came across a table that has multiple records on the same column, something like this:

| Column Header |

Record1; Record2; Record3

I wanted to ask if there is any way in SQL to separate those records using the ; as a separator. I ask this because remaking the database isn't an option at the moment.

so, you mean to say that you want those records to be seperated in rows...right? — Pranav Bilurkar, Dec 03 '15 at 10:53
This is asked almost daily on Stackoverflow and is a duplicate of so many questions... None however have answers quite as good as the answers provided by a series of articles by Aaron Bertrand – [Split strings the right way – or the next best way]( sqlperformance.com/2012/07/t-sql-queries/split-strings), [Splitting Strings : A Follow-Up](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up) , and [Splitting Strings : Now with less T-SQL](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql). — GarethD, Dec 03 '15 at 10:59

score 1 · Answer 1 · answered Dec 03 '15 at 10:56

1

fast solution -

DECLARE @T TABLE
(
    Col NVARCHAR(2000),
    Col1 AS PARSENAME(REPLACE(Col, '; ', '.'), 3),
    Col2 AS PARSENAME(REPLACE(Col, '; ', '.'), 2),
    Col3 AS PARSENAME(REPLACE(Col, '; ', '.'), 1)
)

INSERT INTO @T (Col)
VALUES ('Record1; Record2; Record3')

SELECT * FROM @T

good solution - normalize table

answered Dec 03 '15 at 10:56

Devart

119,203
23
166
186

Fairly strong caveats - limited to 3 delimiters and relies on none of the strings being split containing `.`. – GarethD Dec 03 '15 at 11:00

score 1 · Accepted Answer · edited May 23 '17 at 11:52

DECLARE @badData TABLE (id INT NOT NULL, txt NVARCHAR(max));

INSERT INTO @badData
VALUES (1, 'foo,bar,baz'), (2, NULL);;

-- the idea is to recursively 'pop' a value from the start of the string, splitting it into 'head' and 'tail' components
WITH unpacked (id, head, tail)
AS (
  SELECT id, LEFT(txt, CHARINDEX(',', txt + ',') - 1), STUFF(txt, 1, CHARINDEX(',', txt + ','), '')
  FROM @badData

  UNION ALL

  SELECT id, LEFT(tail, CHARINDEX(',', TAIL + ',') - 1), STUFF(tail, 1, CHARINDEX(',', tail+ ','), '')
  FROM unpacked
  WHERE tail > ''
  )
SELECT id, head
FROM unpacked
ORDER BY id

heavily plagiarised from https://stackoverflow.com/a/5493616/6722

Separating records in same Database column

2 Answers2