We help IT Professionals succeed at work.

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!
Comment
Watch Question

Commented:
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

Commented:
try this :

select ADDRESS1 =
CASE
WHEN CHARINDEX (ADDRESS,CHAR(13)) = 0 THEN ADDRESS
ELSE SUBSTRING (ADDRESS,1,CHARINDEX(ADDRESS,CHAR(13))-1)
END
, ADDRESS2=
CASE
WHEN CHARINDEX (ADDRESS,CHAR(13)) = 0 THEN ""
WHEN CHARINDEX (ADDRESS,CHAR(13),CHARINDEX(ADDRESS,CHAR(13))+1) = 0 THEN ""
ELSE SUBSTRING (ADDRESS,CHARINDEX(ADDRESS,CHAR(13))+1,LEN(ADDRESS)-CHARINDEX(ADDRESS,CHAR(13))+1)
END

FROM ...

Author

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.