asked on

SQL Query parsing one field into many

We have a sql server database that stores a multiline customer address in a single field. I found out that a carriage return is what separates each line.

Here is an example of data

ADDRESS
*************
123 Main StApt 1Some City, MyState 12345
*************

The address shows up in our erp as
*****************************************
123 Main St
Apt 1
Some City, MyState 12345
*******************************************

What I want to extract are the two address lines in a SQL query:
123 Main St as address1
Apt 1 as address2

Any suggestions? Thanks!

jogos

Replace the cariage return ( char(13) ) by a space.

replace(adress,char(13),' ')

Open in new window

Could also be cariage return/lineed

replace(adress,char(13)+char(10),' ')

Open in new window

ASKER CERTIFIED SOLUTION

cy_hung

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mossmis

ASKER

I tried cy hung's code and I wasn't getting results. After some research, I gave some bad information. It wasn't a carriage return that was stored rather a line feed char(10). I replaced that in the code and got better results.

select
SUBSTRING(ADDRESS,1,CHARINDEX(CHAR(10),ADDRESS)-1) as address1,
SUBSTRING (ADDRESS,CHARINDEX(CHAR(10),ADDRESS)+1,LEN(ADDRESS)-CHARINDEX(CHAR(10),ADDRESS)+1) as address2
from dbo.address

Thanks for the guidance.