SQL how to cut off string

Question

I have a column of strings with a city, state and number in each.

SPOKANE, WA           232/107
LAS VEGAS, NV         232/117
PORTLAND, OR          232/128

There are many more than just that, but I am wondering how either I could cut off the numbers in this column and just show the city and state or -even better- cut off the numbers and make city and state a separate column.

The column is in the same format all the way down for all the different records.

Thanks!

Solution 1

Without doing all of the work for you...

City: A substring of the column from position 0, to the first occurence of a comma - 1.
State: A substring of the column from 2 positions after the first occurence of a comma, to the next position that is a space... trimmed.

see: SUBSTRING(), CHARINDEX(), PATINDEX()

Solution 2

I already figured it out and had written the SQL... then I saw Fosco's answer, but since I have it I might as well post it anyway:

SELECT
    LEFT(yourcolumn, CHARINDEX(',', yourcolumn) - 1) AS City,
    RIGHT(LEFT(yourcolumn, CHARINDEX(',', yourcolumn) + 3), 2) AS State
FROM yourtable

The difference between this algorithm and Fosco's is that this assumes that the state is exactly 2 letters. If that is not always true then you should use the other answer.

Solution 3

To remove the numbers at end use the substring method, something like this.

@str = SUBSTRING(@str, LEN(@str)-7, 7)

To separate the city and state you'll need some kind of split function but I can't remember the syntax off top of my head sorry.