MS-Access: Replace "bracket"

Question

Hi,

In one of the ms-access table I work with we have a text field with a set size. At the end of this field there is some extra code that varies depending on the situation. I'm looking for a way to remove one of these code but even when the last part is truncated by the field maximum size.

Let's call the field "field" and the code I'm looking to remove "abc-longcode".

If I use the replace SQL function with the string abc-longcode the query will only work when the code is complete.

If I also want my update query (that does nothing but remove this specific code at the end of my field) to work on incomplete codes how would that translate into ms-SQL?

It would have to remove (or replace with "" to be precise) all of the following (example of course, not the real codes):

abc-longcode
abc-longcod
abc-longco
abc-longc
abc-long
abc-lon
abc-lo
abc-l

Obviously I could do that with several queries. Each one replacing one of the expected truncated codes... but it doesn't sound optimal.

Also, when the field is big enough to get all of the code, there can sometime be extra details at the end that I'll also want to keep so I cannot either just look for "abc-l" and delete everything that follows :\

This query (or queries if I can't find a better way) will be held directly into the .mdb database.

So while I can think of several ways to do this outside of a ms-sql query, it doesn't help me.

Any help? Thanks.

Answer 1

You can write a custom VBA replace method that will replace any of the given cases {"abc-longcode", ... "abc-l"}. This is essentially the same tack as your "several queries" idea, except it would only be one query. My VBA is rusty, but something like:

public function ReplaceCodes(str as string) as string
     dim returnString as string

     returnString = str
     returnString = replace(returnString,"abc-longcode","")
     // ... etc...

     ReplaceCodes = returnString
end function

I may have gotten the parameter order wrong on replace :)

Answer 2

I would use my own custom function to do this using the split function to get the first part of the string. You can then use that value in the update query.

Public Function FirstPart(thetext As String) As String
    Dim ret As String
    Dim arrSplitText As Variant

    arrSplitText = Split(thetext, "-")
    ret = arrSplitText(0)

    FirstPart = ret
End Function

Answer 3

Can you use:

 Left(FieldX,InStr(FieldX,"abc-")-1) 

EDIT re Comment

If there is a space or other standard delimiter:

 IIf(InStr(InStr(FieldX, "abc-"), FieldX, " ") = 0, Left(FieldX, InStr(FieldX, "abc-") - 1), Replace(FieldX, Mid(FieldX, InStr(FieldX, "abc-"), InStr(InStr(FieldX, "abc-"), FieldX, " ") - InStr(FieldX, "abc-")), ""))