Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Parsing a string of data

Author  Topic 

ravenhawk08
Starting Member

8 Posts

Posted - 2013-08-12 : 14:57:50
Need some guidance from the experts. I have a field of data that contains demographic data about customers. The data MAY contain multiple addresses (if the customer has moved,etc).

What I need to do is parse the field so the addresses are stored in separate columns so I can compare them against another table so see if there are any matches. Below is an example of the data field I'm trying to parse out. The addresses may be of different lengths and content (e.g. may / may not include city, etc)

Addr:123 Main Street, Columbus OH; Addr:3456 Wilson Road, Denver CO; Addr:798 Lewis, VA

Ultimately what I need to see is something like this:
Addr1 Addr2 Addr3
123 Main Street 3456 Wilson Road 798 Lewis

Any assistance/suggestions would be appreciated.

Thank you

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-12 : 15:07:03
quote:
Originally posted by ravenhawk08

Need some guidance from the experts. I have a field of data that contains demographic data about customers. The data MAY contain multiple addresses (if the customer has moved,etc).

What I need to do is parse the field so the addresses are stored in separate columns so I can compare them against another table so see if there are any matches. Below is an example of the data field I'm trying to parse out. The addresses may be of different lengths and content (e.g. may / may not include city, etc)

Addr:123 Main Street, Columbus OH; Addr:3456 Wilson Road, Denver CO; Addr:798 Lewis, VA

Ultimately what I need to see is something like this:
Addr1 Addr2 Addr3
123 Main Street 3456 Wilson Road 798 Lewis

Any assistance/suggestions would be appreciated.

Thank you

Usually this is a hard problem because of the variability of the data. There may be street addresses that have comma's in there etc. However, if you can always make the assumption that each address is separated by a semi-colon from the next, and the first comma for each address marks the end of street address, then you can do the following:
DECLARE @addresses VARCHAR(1024) = 
'123 Main Street, Columbus OH; Addr:3456 Wilson Road, Denver CO; Addr:798 Lewis, VA';

SELECT
LEFT(Item ,CHARINDEX(',',Item+',')-1)
FROM
dbo.DelimitedSplit8K(@addresses,';')
I would recommend keeping it in this column format rather than pivoting it. It will be easier to query and modify. For example, you could mark one as primary address. You could accommodate more than 3 addresses etc.

The DelimitedSplit8K is a string splitter function that Jeff Moden wrote. You can find it here in Figure 21: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Go to Top of Page

kameswararao polireddy
Starting Member

19 Posts

Posted - 2013-08-28 : 11:53:01
quote:
Originally posted by ravenhawk08

Need some guidance from the experts. I have a field of data that contains demographic data about customers. The data MAY contain multiple addresses (if the customer has moved,etc).

What I need to do is parse the field so the addresses are stored in separate columns so I can compare them against another table so see if there are any matches. Below is an example of the data field I'm trying to parse out. The addresses may be of different lengths and content (e.g. may / may not include city, etc)

Addr:123 Main Street, Columbus OH; Addr:3456 Wilson Road, Denver CO; Addr:798 Lewis, VA

Ultimately what I need to see is something like this:
Addr1 Addr2 Addr3
123 Main Street 3456 Wilson Road 798 Lewis

Any assistance/suggestions would be appreciated.

Thank you



P.Kameswara rao
Go to Top of Page
   

- Advertisement -