Using SUBSTRING and RTRIM Together I have a VARCHAR field that contains a city and a 2 digit state abbreviation, but there tends to be leading and trailing spaces in the field.  I need to parse out the City and State Abbrev into separate fields. Currently I just have this to parse out the State, but I have no idea how to handle the City

RIGHT(RTRIM(alt.Address),2)
Try this

select right(rtrim(alt.Address),2),
   rtrim(left(ltrim(alt.Address),len(ltrim(rtrim(alt.Address))) - 2))


A slight variation on the second expression (city), which works the same but uses fewer ltrim/rtrim calls (which makes it more concise, albeit not necessarily clearer; it depends on how explicit you prefer the logic to be):

select right(rtrim(alt.Address),2),
   ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))


You can play with both options in this live demo at dbfiddle.uk.