split a sql field based on carriage return

[Tonyvee73]

Hi All
My first post here, hope someone can help me?
I have a table named SL_ADDRESSES and a field in there named (AD_ADDRESS. This has 4 lines or so of data all with carriage returns at the end of each line. When i select the AD_ADDRESS field all data is in one field. Is there a way to separate this into separate fields based on lines of data before the carriage return? Really am stuck.
Thanks

 

[Andrzejek]

From thread222-1808343:
"sorry, i mean how do i get into my SELECT query in sql?"

What I would like to know:
Show the example of the data you have, and
show what you want it to look like as the outcome.

So, if your data looks like:
[pre]
AD_ADDRESS
----------------
123 Main Street
Some City, CA
90210
----------------
876 Center Ave
Springfield
US 12345
----------------
[/pre]
The output would be: ???

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[imex]

Hi,

If you are using SQL Server 2016 or later you can use the String_Split function:

select value
from SL_ADDRESSES
cross apply STRING_SPLIT((AD_ADDRESS, CHAR(13))
where RTRIM(value) <> ''

Hope this help

[URL unfurl="true"]https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]