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