Link to home
Start Free TrialLog in
Avatar of Junior Vasquez
Junior VasquezFlag for El Salvador

asked on

Parsing Data

I want to get everything from the first character to the word United States, for example:

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.
Avatar of Arana (G.P.)
Arana (G.P.)

left(YourOrignalString,charindex ('United States',YourOriginalString)-1)

or you can try

left(YourOrignalString,len(YourOrignalString)-charindex ('U',reverse(YourOriginalString))-1)
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great Code Scott
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.
Avatar of Junior Vasquez

ASKER

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. 

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

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