CODEWITHSUNDEEP
ms-accessms-access-2007ms-access-2010
Learner
Learner

Reputation: 353

Remove last characters after last space in a string

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 Server 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?

I have tried these and doesn't help

1st Method

Left([ColumnName], Len([ColumnName]) -1)

2nd Method

SELECT LEFT(ColumnName, INSTRREV(ColumnName, " "))

Upvotes: 2

Views: 6388

Answers (3)

rchacko
rchacko

Reputation: 2119

Try this to remove everything after last space character in a string

DECLARE @string AS VARCHAR(100)='First Second Third'

select LEFT(@string, LEN(@string) - CHARINDEX(' ', REVERSE(@string)))

This will give you:

First Second

Upvotes: -1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112362

From your comments it has become clear that the problem that you face, is to detect whether and how many characters have to be removed at the end. Since you are removing characters without testing if there is actually an abbreviated middle name there, neither your SQL-Server nor your Access-SQL solutions will work! Worse, if the Column is null or does not contain any space characters, then you might even get an exception.

I suggest to create a VBA Function and to call this one from your Access query, since the logic gets a bit complicated.

Public Function RemoveMiddleName(ByVal name As Variant) As Variant
    RemoveMiddleName = name
    If Not IsNull(name) Then
        Dim s As String, pos As Long, l As Long
        s = CStr(name)
        pos = InStrRev(s, " ")
        l = Len(s)
        If pos > 1 And l - pos <= 2 Then '1 or 2 characters at the end
            If l - pos = 1 Or (l - pos = 2 And Right$(s, 1) = ".") Then
                RemoveMiddleName = Left(s, pos - 1)
            End If
        End If
    End If
End Function

Place this code in a VBA Module (not in a Form or Report).

And then call it like this:

SELECT RemoveMiddleName([ColumnName]) As FirstLast FROM MyTable

Upvotes: 3

Don George
Don George

Reputation: 1328

Trim(Left([columnname] & "  ",InStr(1+InStr([columnname] & "  "," "),[columnname] & "  "," ")))

Note the double space after each of the & - this ensures there are always at least 2 spaces in the search field.

Upvotes: 0

Related Questions