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

  • Is there a delimiter between City and State like a comma? Or a space between? And is it consistent? – Jacob H May 8 '19 at 21:39

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.

| improve this answer | |
  • You can make the second expression simpler: left(alt.Address, len(alt.Address) - 2). Apply ltrim and/or rtrim to the result of left if necessary. That works because len ignores trailing spaces. – Andriy M May 8 '19 at 21:54
  • Thanks for sharing, I was not aware len() ignores the trailing spaces. However wouldn't you still need a ltrim() on the first parameter to left ? In the case of leading spaces. – kevinnwhat May 8 '19 at 21:58
  • 1
    As I said, you can apply either ltrim or rtrim, or both, after applying the left, i.e. to the result of left. – Andriy M May 8 '19 at 21:59
  • 1
    This actually still works with spaces in between city and state abbreviation, give it a shot. – kevinnwhat May 8 '19 at 22:05
  • 1
    I took the liberty of adding the second option, as it's just a variation on your solution. If you don't like it, feel free to roll back, I don't mind, cheers – Andriy M May 8 '19 at 22:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.