Remove last character after last space in a string

• vigneshkumart50

  Ten Centuries

  Points: 1205

  More actions

  September 22, 2014 at 5:20 am

  #74384

  Hi All,

  I'm having string values like mentioned below.

  String Values:

  George, Matt J

  Rambla, Tony G.

  Expected Output:

  George, Matt

  Rambla, Tony

  I would need to trim the letters like mentioned above. Can anyone help me with this?

  I achieved the same in SQL by below query

  declare @String as varchar(20) = 'Rambla, Tony G.'

  select left(@string,len(@string)+1-charindex(' ',reverse(@string)))

  How can I achieve this in MS Access?

• WendellB

  SSCrazy Eights

  Points: 8678

  More actions

  September 22, 2014 at 5:34 am

  #1747005

  There is a function in Access that does a reverse search on a string - it is InstrRev(). Doing that will let you determine the position of the last space in the string, and then prune it accordingly with the Len() function. That presumes that you always have two spaces in the string, which it appears your T-SQL also does.

  Wendell
  Colorful Colorado
  You can't see the view if you don't climb the mountain!