1

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)
Improve this question
1
  • Is there a delimiter between City and State like a comma? Or a space between? And is it consistent?
    – Jacob H
    May 8, 2019 at 21:39

1 Answer 1

Reset to default
3

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
7
  • 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, 2019 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, 2019 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, 2019 at 21:59
  • 1
    This actually still works with spaces in between city and state abbreviation, give it a shot.
    – kevinnwhat
    May 8, 2019 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, 2019 at 22:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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