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.
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, VAUltimately what I need to see is something like this:Addr1 Addr2 Addr3123 Main Street 3456 Wilson Road 798 LewisAny 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, VAUltimately what I need to see is something like this:Addr1 Addr2 Addr3123 Main Street 3456 Wilson Road 798 LewisAny 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/ |
|
|
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, VAUltimately what I need to see is something like this:Addr1 Addr2 Addr3123 Main Street 3456 Wilson Road 798 LewisAny assistance/suggestions would be appreciated. Thank you
P.Kameswara rao |
|
|
|
|
|
|
|