SQL how to cut a string

Question

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

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

There is much more than just, but I wonder how I could cut off the numbers in this column and just show the city and state, or even better reduce the numbers and make the city and specify a separate column.

The column is in the same format up to all the different records.

Thank!

Answer 1

Without any work for you ...

City: substring of a column from position 0 to the first occurrence of a comma - 1.
State: substring of a column from two positions after the first occurrence of a comma, to the next position, which is the space ... cropped.

.: SUBSTRING(), CHARINDEX(), PATINDEX()

Answer 2

SQL... Fosco, , , :

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

Fosco , , 2 . , .

Answer 3

, , - .

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

, - , , .

Answer 4

. , , , . , , .

substring(), .

: xxx/yyy , . , , : City, State, [WhateverYouCallThatCode]

EDIT , Right(7, [Column])?

Answer 5

, , ...

SELECT
   substring([field], 1, patindex('%[1-9]%', [field])-1) as [CITY_AND_STATE],
   substring([field], 1, charindex(',', [field])-1) as [CITY_ONLY],
   substring([field], charindex(',', [field]), patindex('%[1-9]%', [field])-1)) as [STATE_ONLY],
   substring([field], patindex('%[1-9]%', [field]), len([field])) as [NUMBERS_ONLY]
FROM
   [table]

Answer 6

REVERSE. , , NNN/MMM.

The method worked well for me to parse file paths. The inverted line and the search for the first "\" in the line with the opposite sign indicates where the path ends and the file name begins.

If your data is also formatted as you specify, this method may be more complex than some others. But when the initial data is “chaotic,” this method can really simplify things.