How to split a multi-line string into columns in tsql

Question

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

88 Park View
Hemmingdale
London

Could anyone tell me the best way to get line 1, line 2 & line 3 as distinct fields in a select statement?

Regards Richard

Answer 1

Try something like this? Mind you this is 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