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!
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()
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With