Solved String without Specific Last word (1 Viewer)

[nirmal]

Sir
I have a string field value in a column Place in Place_tbl, for example

123, abdf, sygea, dsead
kasr, dladr, ase, 4896
2123, abdf, sygea, dsead
523, abdf, sygea, dsead
93, abdf, sygea, dsead
125, csd, edrf, dsead
sdfg, 124, jkf, 92hn

if I use the query
shortenedPlace: left(Place,instrrev(place,",")-1)
it removes the last word after comma.
But if I want to remove some specific word like "dsead" and ot any other what should be query
Then what should be the query design

 

[Gasman]

Use Replace() ?

 

[nirmal]

Use Replace() ?

Sir it will only replace the word.
On the contrary I dont want to delete any other last word other than "dsead"
I want to keep the other last words e.g 4896, 92hn which are <> dsead

 

[Gasman]

Well your example has dsead at the end of the line? You specify what is being replaced?
However if it was the first in the line, then it would still be replaced.

So I would write a function to split the data, then inspect the last entry to see if it is the word required. If so then build up the string again with all the other entries, but omit that last one.

This function can also just remove the last of anything, if you do not pass a word in to it?

So it does both of your requirements.

 

[nirmal]

Sir Can you help me with the function creation

 

[Gasman]

I am not going to write it for you. You learn nothing that way, as you will just use it and come back for more, like Oliver.
Investigate the Split() function. Search here, as it has been used many times for issues like this.
You cannot use Split() directly in a query, not would it be suitable in this case with your special word requirement.

As I mentioned previously
split the data, then inspect the last entry to see if it is the word required. If so then build up the string again with all the other entries with & and ", " and omit that last one.

Split function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[Gasman]

In fact @MajP got you started in this thread?

Solved - String without last Word

Sir, I have a string field value in a column Place in Place_tbl, for example 123, abdf, sygea, dsead or kasr, dladr, ase, 4896 I want the result without the last word 123, abdf, sygea kasr, dladr, ase What should be the query design Please Help

www.access-programmers.co.uk

 

[ebs17]

There are two answers to your request:

1) Multiple words as one field content, this is a violation of normalization. With a single word as field content, you can use standard methods of a query. Bad data structures create the need for cumbersome solutions. If your project is to become a real database, you should think very hard about it.

 

[ebs17]

2) Regular expressions are convincing for more complex string manipulations. In the following query, the first calculation deletes the named word in each position. But RegEx is capable of much more.

SELECT
   AnyStringfield,
   RegExReplace(AnyStringfield, "\bdsead\b", "") AS NewField1,
   RegExReplace(AnyStringfield, "\bdsead\b$", "") AS NewField2,
   RegExReplace(AnyStringfield, "\b(dsead|123)\b", "") AS NewField3
FROM
   TableX

The required function must be stored in a standard module:

Private pRegEx As Object

Public Property Get oRegEx() As Object
   If (pRegEx Is Nothing) Then Set pRegEx = CreateObject("Vbscript.Regexp")
   Set oRegEx = pRegEx
End Property

Public Function RegExReplace(ByVal SourceText As String, _
      ByVal SearchPattern As String, _
      ByVal ReplaceText As String, _
      Optional ByVal bIgnoreCase As Boolean = True, _
      Optional ByVal bGlobal As Boolean = True, _
      Optional ByVal bMultiLine As Boolean = True) As String
 
   With oRegEx
      .Pattern = SearchPattern
      .IgnoreCase = bIgnoreCase
      .Global = bGlobal
      .Multiline = bMultiLine
      RegExReplace = .Replace(SourceText, ReplaceText)
   End With
End Function

 

[arnelgp]

this is a little longer, but will deal with those delimiters properly when
the Text is remove,

example to remove "123" and "abdf" from the string: "123, abdf, sygea, dsead"

fncRemoveTExt("123, abdf, sygea, dsead","123","abdf")

' arnelgp
Public Function fncRemoveText(ByVal OrigString As Variant, ParamArray word() As Variant) As Variant
    Dim delim As String, i As Integer
    fncRemoveText = OrigString
    If IsNull(OrigString) Then
        Exit Function
    End If
    'check what is the delimiter
    delim = WhatIsDelim(OrigString)
    For i = 0 To UBound(word)
        OrigString = Replace$(OrigString, word(i), "")
    Next
    Do While InStr(OrigString, delim & delim) <> 0
        OrigString = Replace$(OrigString, delim & delim, delim)
    Loop
    OrigString = Trim$(OrigString)
    delim = Trim$(delim)
    If Left$(OrigString, 1) = delim Then
        OrigString = Mid$(OrigString, 2)
    End If
    If Right$(OrigString, 1) = delim Then
        OrigString = Left$(OrigString, Len(OrigString) - 1)
    End If
    fncRemoveText = Trim$(OrigString)
End Function

' arnelgp
' helper function to fncRemove
' determine the delimiter
Public Function WhatIsDelim(ByVal s As String) As String
    Dim d As Variant, cnt As Integer, tmp As Integer
    Dim i As Integer, k As Integer, delim As String
    d = Array(", ", "; ", ",", ";")
    For i = 0 To 1
        tmp = 0
        For k = 1 To Len(s)
            If Mid$(s & " ", k, Len(d(i))) = d(i) Then
                tmp = tmp + 1
            End If
        Next
        If tmp > cnt Then
            cnt = tmp
            delim = d(i)
        End If
    Next
    If cnt = 0 Then
        For i = 2 To 3
            tmp = 0
            For k = 1 To Len(s)
                If Mid$(s & " ", k, Len(d(i))) = d(i) Then
                    tmp = tmp + 1
                End If
            Next
            If tmp > cnt Then
                cnt = tmp
                delim = d(i)
            End If
        Next
    End If
    WhatIsDelim = delim
End Function