Problems splitting values ​​into multiple lines using T-SQL

Question

I have a Sql Server 2K8 R2 DB with a table in which column values ​​contain values ​​separated by characters (char 13 and char 10).

I am creating a script to import data into a properly normalized schema.

There is something like this in my source table:

ID | Value ________________ 1 | line 1 line 2 ________________ 2 | line 3 ________________ 3 | line 4 line 5 line 6 ________________ 

etc.

[edit] FYI, Id is an integer, and the value is nvarchar (3072) [/ edit]

What I want is to query the table so that it looks like this:

 ID | Value ________________ 1 | line 1 ________________ 1 | line 2 ________________ 2 | line 3 ________________ 3 | line 4 ________________ 3 | line 5 ________________ 3 | line 6 ________________ 

I read a lot of answers here on SO, as well as on the Internet, and I believe that using master..sptvalues should be the solution. I especially tried to reproduce the solution to the question Split one column into several rows . However, without success (suspecting the presence of two characters causing problems).

Now I wrote this query:

 SELECT T.ID, T.Value, RIGHT(LEFT(T.Value,spt.Number-1), CHARINDEX(char(13)+char(10),REVERSE(LEFT(char(13)+char(10)+T.Value,spt.Number-1)))) as Extracted FROM master..spt_values spt, ContactsNew T WHERE Type = 'P' AND spt.Number BETWEEN 1 AND LEN(T.Value)+1 AND (SUBSTRING(T.Value,spt.Number,2) = char(13)+char(10) OR SUBSTRING(T.Value,spt.Number,2) = '') 

This request, unfortunately, is returned:

 ID | Value | Extracted ________________________________ 1 | line 1 | <blank> line 2 | ________________________________ 1 | line 1 | line 2 line 2 | ________________________________ 2 | line 3 | <blank> ________________________________ 3 | line 4 | <blank> line 5 | line 6 | ________________________________ 3 | line 4 | line 5 line 5 | line 6 line 6 | ________________________________ 3 | line 4 | line 6 line 5 | line 6 | ________________________________ 

<blank> is an empty string, not an empty string.

I would appreciate help setting up my request.

[Edit2] My source table contains less than 200 entries, and performance is optional, so I am aiming for a simple solution, not an efficient one [Edit2]

[Edit3] The source database is read-only. I cannot add a stored procedure, function or type clr. I have to do this in one request. [Edit3]

[Edit4] Something strange ... it seems that spaces are also considered as delimiters.

If I run the following query:

 SELECT T.ID, replace(T.Value, '#', ' '), replace(RIGHT( LEFT(T.Value,spt.Number-1), CHARINDEX( char(13) + char(10),REVERSE(LEFT(char(10) + char(13)+T.Value,spt.Number-0))) ), '#', ' ') FROM master..spt_values spt, ( select contactID, replace(Value,' ', '#') Value from ContactsNew where Value is not null ) T WHERE Type = 'P' AND spt.Number BETWEEN 1 AND LEN(T.Value)+1 AND (SUBSTRING(T.Value,spt.Number,2) = char(13) + char(10) OR SUBSTRING(T.Value,spt.Number,1) = '') 

I received the correct number of returns (however, still having the wrong values) during the execution of this request:

 SELECT T.ID, T.Value, RIGHT( LEFT(T.Value,spt.Number-1), CHARINDEX( char(13) + char(10),REVERSE(LEFT(char(10) + char(13)+T.Value,spt.Number-0))) ) FROM master..spt_values spt, ( select contactID, Value from ContactsNew where Value is not null ) T WHERE Type = 'P' AND spt.Number BETWEEN 1 AND LEN(T.Value)+1 AND (SUBSTRING(T.Value,spt.Number,2) = char(13) + char(10) OR SUBSTRING(T.Value,spt.Number,1) = '') 

splits into spaces also

Answer 1

EDIT # 1: I deleted the source text of the response. Try the following query. I changed your logic a bit. If you have any questions, feel free to ask in the comments. If you need another separator, just enter another subquery to replace that separator with CHAR (13) + CHAR (10).

 SELECT * FROM ( SELECT T.ID, T.Value, CASE WHEN CHARINDEX(CHAR(13) + CHAR(10), SUBSTRING(T.Value, spt.number, LEN(T.Value) - spt.Number + 1)) > 0 THEN LEFT( SUBSTRING(T.Value, spt.number, LEN(T.Value) - spt.Number + 1), CHARINDEX(CHAR(13) + CHAR(10), SUBSTRING(T.Value, spt.number, LEN(T.Value) - spt.Number + 1)) - 1) /* added by Steve B. see comments for the reasons */ when len(T.Value) = spt.Number then right(t.Value, spt.number -1) /* end of edit */ ELSE SUBSTRING(T.Value, spt.number, LEN(T.Value) - spt.Number + 1) END EXTRACTED FROM master..spt_values spt, ContactsNew T WHERE Type = 'P' AND spt.Number BETWEEN 1 AND LEN(T.Value)+1 ) X WHERE EXTRACTED <> '' AND ( LEFT(X.VALUE, LEN(EXTRACTED)) = EXTRACTED OR X.Value LIKE '%' + CHAR(13) + CHAR(10) + EXTRACTED + CHAR(13) + CHAR(10) + '%' OR X.Value LIKE '%' + CHAR(13) + CHAR(10) + EXTRACTED ) 

Answer 2

An example query that shows how to perform this type of operation with some test data similar to that described.

If you cannot declare variables in your final statement, you can find / replace them for your values, but this makes things a little easier.

This works by replacing CR+LF with one character before doing the split.
If '|' used in your data, select another single character that will not be used as a time separator.

 declare @crlf nvarchar(2) = char(10) + char(13) declare @cDelim nvarchar(1) = N'|' -- test data declare @t table (id int ,value nvarchar(3072)) insert @t select 1, 'line1' + @crlf + 'line2' union all select 2, 'line3' union all select 3, 'line4' + @crlf + 'line5' + @crlf + 'line6' -- /test data ;WITH charCTE AS ( --split the string into a dataset SELECT D.id, D.value, SUBSTRING(Ds,n,CHARINDEX(@cDelim, Ds + @cDelim,n) -n) AS ELEMENT FROM (SELECT id, value, REPLACE(value,@crlf,@cDelim) as s from @t) AS D JOIN (SELECT TOP 3072 ROW_NUMBER() OVER (ORDER BY a.type, a.number, a.name) AS n FROM master.dbo.spt_values a CROSS JOIN master.dbo.spt_values b ) AS numsCte ON n <= LEN(s) AND SUBSTRING(@cDelim + s,n,1) = @cDelim ) SELECT id, ELEMENT FROM charCTE order by id, element