SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Parsing a string of data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ravenhawk08
Starting Member

8 Posts

Posted - 08/12/2013 :  14:57:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 08/12/2013 :  15:07:03  Show Profile  Reply with Quote
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

India
19 Posts

Posted - 08/28/2013 :  11:53:01  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000