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 |
|
simflex
Constraint Violating Yak Guru
327 Posts |
 Posted - 2004-01-22 : 08:35:12
|
| This is a bit too complicated for me right now.We have a client who sends to us addresses of their own clients.These addresses are usually on one line.For instance,2600 DevWineverine rd, suite 200,Detroit, Michigan 30134.The important thing to note about above address is the first line.We are given a database that is designed with a 35 character address field.Which means that if the address is longer than 35 characters, as has been the case several times, it will be truncated.Our best solution would be to split the address into 2 and call the first part "address1" and the second part "address2".This way, the above address would like this:Address1: 2600 DevWineverine rdAddress2: suite 200The problem we have so far is how can these addresses be dynamically split into address1 and address2 in such way that we won't run into a situation where we would have something like:Address1: 2600 DevWineverAddress2: ine rd Suite 200?We would just like full address on address1 and additional info like suites or apartment numbers on address line 2.Does anyone have an idea?Hope this question is clear. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-01-22 : 08:45:25
|
| Write a UDF.Have the UDF accept the string and a "Address line#" as an argument -- either 1 or 2.Your algorithm: Start at position 36 and work backwards until you find a space character. Call the position of the space character P.Then, you can return:AddressLine 1 = RTRIM(Left(String,P-1))orAddressLine 2 = SUBSTRING(String,P+1,1000)However -- note that this may still return an address in the form of:100 Newbury St Apt#1To avoid that scenerio might be pretty tricky. But essentially, what it sound like you need to do is a "word wrap" between the two address columns.- Jeff |
 |
|
|
|
|
|
|
|
Splitting Address Info