troubleshooting Question
stripping data from a Varchar feild in Crystal 2008
asked on
Good Morning Experts,
I have a field in a SQL 2005 database that is "Address Line 1" which contains street number, street name and in some cases unit numbers as well (sometimes proceeded by a comma, someties not. I need to export this data as two or if unit number exists then three seperate feilds, "Street Number" "Street Name" and "Unit Number" and I need to remove the comma if one exists as this will be sent as a comma delimeted file in the end. In addition the numeric portion of the address for example "123 anystreet" sometimes will contains a dash, for example "12-24 anystreet". I need to maintain the dash character in it's existing location. Any ideas?
To give an example of the variations I've seen in the data I've got:
"123 anystreet"
"123 anystreet, Unit 1"
"123 anystreet Unit 1"
"123-45 anystreet"
"123-45 anystreet, Unit 1"
"123-45 anystreet Unit1"
"123-45 anystreet Unit 1c"
"123-45 anystreet unit 1-3c & 2-6b"
I have a field in a SQL 2005 database that is "Address Line 1" which contains street number, street name and in some cases unit numbers as well (sometimes proceeded by a comma, someties not. I need to export this data as two or if unit number exists then three seperate feilds, "Street Number" "Street Name" and "Unit Number" and I need to remove the comma if one exists as this will be sent as a comma delimeted file in the end. In addition the numeric portion of the address for example "123 anystreet" sometimes will contains a dash, for example "12-24 anystreet". I need to maintain the dash character in it's existing location. Any ideas?
To give an example of the variations I've seen in the data I've got:
"123 anystreet"
"123 anystreet, Unit 1"
"123 anystreet Unit 1"
"123-45 anystreet"
"123-45 anystreet, Unit 1"
"123-45 anystreet Unit1"
"123-45 anystreet Unit 1c"
"123-45 anystreet unit 1-3c & 2-6b"
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Learn from the best
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.