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.
or you can try
left(YourOrignalString,len
I'd use a CROSS APPLY to do the searches, then a COALESCE to find the correct part of the original string to pull, something like this:
SELECT
address AS original_address,
LEFT(address, COALESCE(NULLIF(us1, 0), NULLIF(us2, 0), NULLIF(us3, 0),
NULLIF(us4, 0)) - 1) AS address_minus_usa
FROM ( VALUES
('123 Grand Concourse, Bronx, NY 10451 United States'),
('234 Broad Street, Fusion Junction, CO 22222 USA'),
('345 Any Cove, Somewhere, MT 33333 U.S.'),
('456 ZZZ, YYY, ID 44444 US'),
('999 Other, Berlin, Germany')
) AS sample_data(address)
CROSS APPLY (
SELECT CHARINDEX('United Sta', address) AS us1,
PATINDEX('%[^A-Z]USA%', address) AS us2,
PATINDEX('%[^A-Z]U.S.%', address) AS us3,
PATINDEX('%[^A-Z]US[^A-Z]%', address + '/') AS us4
) AS ca1
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.
>>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. <<
Not sure I understand what you want but here is what I think you want (considering only Bronx and nothing else):
select left(ArresLocation,PATINDEX('%[^0-9A-z]Bronx%',ArresLocation)-1-(PATINDEX('%[A-Za-z0-9]%',REVERSE(left(ArresLocation,PATINDEX('%[^0-9A-z]Bronx%',ArresLocation)-1)))))
that will result in:
"123 Grand Concourse"
I added something else after the first "-1" , to take care of any extra commas.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial