How to split a multiline row into columns in tsql

Question

I have a database field containing address information stored as multi-line strings.

88 Park View Hemmingdale London 

Can someone tell me the best way to get row 1, row 2 and row 3 as separate fields in a select statement?

Relationship Richard

Answer 1

Try something like this? Keep in mind that this is a bit vulnerable.

 DECLARE @S VARCHAR(500), @Query VARCHAR(1000) SELECT @S='88 Park View Hemmingdale London' SELECT @Query=''''+ REPLACE(@S, CHAR(13),''',''')+'''' EXEC('SELECT ' +@Query ) 

results

 88 Park View | Hemmingdale | London 

Answer 2

Here is a solution I came up with using CTE to recursively loop through a table of values ​​and split them into a new CHAR(13) line CHAR(13) , and then use PIVOT to display the results. You can expand this to additional columns (over 5) by simply adding them to the PIVOT .

 DECLARE @Table AS TABLE(ID int, SomeText VARCHAR(MAX)) INSERT INTO @Table VALUES(1, '88 Park View Hemmingdale London') INSERT INTO @Table VALUES(2, '100 Main Street Hemmingdale London') INSERT INTO @Table VALUES(3, '123 6th Street Appt. B Hemmingdale London') ;WITH SplitValues (ID, OriginalValue, SplitValue, Level) AS ( SELECT ID, SomeText, CAST('' AS VARCHAR(MAX)), 0 FROM @Table UNION ALL SELECT ID , SUBSTRING(OriginalValue, CASE WHEN CHARINDEX(CHAR(13), OriginalValue) = 0 THEN LEN(OriginalValue) + 1 ELSE CHARINDEX(CHAR(13), OriginalValue) + 2 END, LEN(OriginalValue)) , SUBSTRING(OriginalValue, 0, CASE WHEN CHARINDEX(CHAR(13), OriginalValue) = 0 THEN LEN(OriginalValue) + 1 ELSE CHARINDEX(CHAR(13), OriginalValue) END) , Level + 1 FROM SplitValues WHERE LEN(SplitValues.OriginalValue) > 0 ) SELECT ID, [1] AS Level1, [2] AS Level2, [3] AS Level3, [4] AS Level4, [5] AS Level5 FROM ( SELECT ID, Level, SplitValue FROM SplitValues WHERE Level > 0 ) AS p PIVOT (MAX(SplitValue) FOR Level IN ([1], [2], [3], [4], [5])) AS pvt 

Results:

 ID Level1 Level2 Level3 Level4 Level5 ----------- -------------------- -------------------- -------------------- -------------------- -------------------- 1 88 Park View Hemmingdale London NULL NULL 2 100 Main Street Hemmingdale London NULL NULL 3 123 6th Street Appt. B Hemmingdale London NULL 

Answer 3

Just for fun, updated with an XML solution. This should handle "." and can easily spread over more lines.

 DECLARE @xml XML , @S VARCHAR(500) = '88 Park View Hemmingdale London' SET @xml = CAST('<row><col>' + REPLACE(@S,CHAR(13),'</col><col>') + '</col></row>' AS XML) SELECT line.col.value('col[1]', 'varchar(1000)') AS line1 ,line.col.value('col[2]', 'varchar(1000)') AS line2 ,line.col.value('col[3]', 'varchar(1000)') AS line3 FROM @xml.nodes('/row') AS line(col) 

Answer 4

I know this is terrible, but this way you have 3 rows in 3 columns with the names l1, l2 and l3:

 declare @input nvarchar(max) = 'line 1' + CHAR(13) + CHAR(10) + 'line 2' + CHAR(13) + CHAR(10) + 'line 3' + CHAR(13) + CHAR(10) + 'line 4' + CHAR(13) + CHAR(10) + 'line 5' declare @delimiter nvarchar(10) = CHAR(13) + CHAR(10) declare @outtable table (id int primary key identity(1,1), data nvarchar(1000) ) declare @pos int = 1 declare @temp nvarchar(1000) if substring( @input, 1, len(@delimiter) ) = @delimiter begin set @input = substring(@input, 1+len(@delimiter), len(@input)) end while @pos > 0 begin set @pos = patindex('%' + @delimiter + '%', @input) if @pos > 0 set @temp = substring(@input, 1, @pos-1) else set @temp = @input insert into @outtable ( data ) values ( @temp ) set @input = substring(@input, @pos+len(@delimiter), len(@input)) end select top 1 ISNULL(a.data,'') as l1, ISNULL(b.data,'') as l2, ISNULL(c.data,'') as l3 from @outtable a left outer join @outtable b on b.id = 2 left outer join @outtable c on c.id = 3 

sqlfiddle here