Avatar of Junior Vasquez
Junior Vasquez
Flag 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.
Microsoft SQL Server

Avatar of undefined
Last Comment
Junior Vasquez

8/22/2022 - Mon
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
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Arana (G.P.)

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.
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. 

Your help has saved me hundreds of hours of internet surfing.
fblack61
Scott Pletcher

@arana


Thank you.  


>> I Would include a 5th one as U.S.A. <<

Good point, and it is already covered by the us3 pattern.

Junior Vasquez

ASKER

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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Junior Vasquez

ASKER

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.