sql - How to Select Multi line text as different single line texts? -

update tbl set col1='multi \r\nline \r\ntext'

i'm using above query set col1 value multiline value.

i wish select col1 3 rows.

i want output for

select col1 tbl

like

col1

---

multi

line

text

to separate string elements use nodes() method of xml data type. use should convert string xml format. replace '\r\n' '</x><x>':

with t (   select id,   cast('<x>'+replace(col1,'\r\n','</x><x>')+'</x>' xml) xmldata    tbl   )  select t.id,        a.c.value('data(.)', 'varchar(100)') col1    t cross apply xmldata.nodes('x') a(c)  

sqlfiddle demo