asked on Parsing Data
if the address is
123 Grand Concourse, Bronx, NY 10451 United States
i want everything but the United States
It would be easy to trim from the right, but sometimes, they use the word US, or USA.... That's why i couldn't use the replace function.
since you are considering specific cases
I Would include a 5th one as U.S.A.
and any others that people may have used in your DB (cannot think of more)
and maybe including lowercase scenarios.
of course it would be ideal if you do not let people input the country this way, if possible.
Thank you guys, and also, this data comes from XML files from an external source, so we don't have any control over it. We have a look table that we verify the addresses on, which is in NYC open data. So it's the only way I could think of to match the addresses.
@arana
Thank you.
>> I Would include a 5th one as U.S.A. <<
Good point, and it is already covered by the us3 pattern.
Arana,
Question
I've used this to do a Join
select left(ArrestLocation,len(ArrestLocation)-charindex ('U',reverse(ArrestLocation))-1) as Address,
ArrestLocation, ArrestingAgency, ArrestLocationPSA, ArrestLocationDistrict
from ArrestInfo
and this does everything I asked for. Now, if I wanted to also scrub out city and state, in my case Bronx, NY how could I do that? I tried to do it, but have come up short, so everything from city, state to the end of the string.
thanks.
I actually used a combination of both your's and Scott's solutions. It works very well, thanks for your help. Till next time, happy Friday.
Junior
or you can try
left(YourOrignalString,len