select city from [city state zip] text string 2

[starbase59]

I have a field where the city, state and zip are one text string separated by spaces. I only want the city, in every case, so I'm looking for everything left of the first space.

Examples of field:
MIAMI FL 33131
EDISON NJ 08837
NEWTON MA 02458

I want:
MIAMI
EDISON
NEWTON
Would I use CharIndex or PatIndex for this? and how?
THanks in advance.

 

[gmmastros]

I'm looking for everything left of the first space.

No. Not really. In the U.S., there are approximately 10,000 cities with a space in the name. New York, New Orleans, Saint Louis, etc....

You could try looking for the first number, and then moving to the left a few character positions. This should work since you (apparently) are dealing with state abbreviations which are always 2 characters. So....

Declare @Temp Table(Data VarChar(100))

Insert Into @Temp Values('MIAMI FL 33131')
Insert Into @Temp Values('EDISON NJ 08837')
Insert Into @Temp Values('NEWTON MA 02458')

Select Left(Data, PatIndex('%[0-9]%', Data)-4)
From   @Temp

Note that there are several ways for this to fail. If there's no zipcode (and therefore no number).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I wanted to reply earlier, but was getting errors from this site. But I see that George pointed out a problem - I haven't thought about cities with a space.

 

[gmmastros]

Actually.... I was thinking about this a little more...

This will probably execute a little faster and not have a problem of failing. This does assume that your data is consistently formatted properly (which it never seems to be for me).

Select Left([City State Zip], Case When Len([City State Zip])-9 > 0 Then Len([City State Zip])-9 Else 0 End)

Or.....

Select Left([City State Zip], PatIndex('%[ ][a-z][a-z][ ][0-9][0-9][0-9][0-9][0-9]', [City State Zip]))

In the previous example, if there is not a (space) followed by 2 letters, another space, and 5 numbers, then the left function will return an empty string. So this will only return cities where the state and zip are well formatted.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian101]

It also might help to work it backward, as hopefully your zip will always be 5 then your 2 digit state. Anything if front of that should be your city.

Simi