CODEWITHSUNDEEP
ms-access
sbozzie
sbozzie

Reputation: 727

Access string between two characters

Hia,

I have a string ( a URL) where I want to get first string in between two characters in an access query.

http://websiteurl.com/sometext-STUFFIWANT-foo-bar

All the text is variable, and there are a varying number of "-" in the URL.

I can get the position of the first occurrence:

Instr([column],"-")+1

and I think I want something like:

Mid(
    [column],
    Instr([column],"-")+1,
    InStr(InStr(1,[column],"-")-1,[column],"-")
)

This is giving me however the first chunk of the string, followed by some of the text after what I want.

I ideally would like: "get position of first -, the position of the second -, and then return all the text between those 2 positions"

I'm going round in circles a bit, and though I think I'm close, I'm no longer making progress.

Upvotes: 2

Views: 16843

Answers (3)

user3484244
user3484244

Reputation: 21

I has the same problem, here is a function you can use, which allows for strings as startbit and endbit delimiters, and finds the middlebit from within the teststring even if the second delimiter isn't found.

Public Function middlebit(startbit, endbit, teststring)
Dim first As Integer
Dim second As Integer
middlebit = ""
If teststring <> "" Then
    first = InStr(1, teststring, startbit)
    second = InStr(first + Len(startbit), teststring, endbit)
    If second = 0 Then second = Len(teststring)
    If first > 0 And second > first Then
        second = second - first
        middlebit = Mid(teststring, first + Len(startbit), second - Len(startbit))
    End If
End If

End Function

Upvotes: 0

HansUp
HansUp

Reputation: 97101

You didn't mention the context where you're doing this ... a VBA procedure, a query in Access, a query from outside an Access session. But this could be very easy if you can use the Split() function. Consider this example from the Immediate window.

strUrl = "http://websiteurl.com/sometext-STUFFIWANT-foo-bar"
varPieces = Split(strUrl, "-")
? varPieces(1)
STUFFIWANT

Now that you've said you want to do this in a query, I think you're looking for something like this ...

SELECT
    Mid(
        [column],
        InStr([column], "-") +1,
        (InStr(InStr([column], "-") +1, [column], "-") - InStr([column], "-")) -1
        ) AS stuff_i_want
FROM YourTable;

That might even be sufficient. However, it will throw "invalid use of Null" errors whenever [column] is Null. And it will also choke on [column] values which include less than 2 dashes. If you can deal with those limitations, you may be good to go. But if you need to modify that complex field expression ... good luck!

If this is a query you will be running from within an Access application session, you could create a user-defined function based on the Split() example I showed you earlier. Then your query would be simple to write.

SELECT
    GetStuffYouWant([column]) AS stuff_i_want
FROM YourTable;

You may have heard warnings that UDFs can be slow in queries. However, in this case, the alternative is Mid() plus a bunch of InStr() functions, and that's not really an easier workload for the db engine. I compared both approaches with a table which contains over 10 million rows. The time it took to fully populate a DAO recordset ranged between 37 and 45 seconds, with neither approach as the consistent winner.

If you want to try the UDF approach yourself, I included the function below. And here is an Immediate window session testing the function with various input values.

? GetStuffYouWant(Null)
Null
? GetStuffYouWant("")
Null
? GetStuffYouWant("abc")
Null
? GetStuffYouWant("abc-")
Null
? GetStuffYouWant("abc-def")
Null
? GetStuffYouWant("abc-def-")
def
? GetStuffYouWant("http://websiteurl.com/sometext-STUFFIWANT-foo-bar")
STUFFIWANT

And the function ...

Public Function GetStuffYouWant(ByVal pInput As Variant, _
        Optional pSplitChar As String = "-") As Variant
    Dim varResult As Variant
    Dim varPieces As Variant

    If IsNull(pInput) Then
        varResult = Null
    Else
        varPieces = Split(pInput, pSplitChar)
        If UBound(varPieces) > 1 Then
            varResult = varPieces(1)
        Else
            varResult = Null
        End If
    End If
    GetStuffYouWant = varResult
End Function

Upvotes: 5

Scotch
Scotch

Reputation: 3226

Your code is really close. Is this what you want to do?

   dim first as integer
  dim second as integer
   dim result as string
   first = instr(1,"yourtext","-")
    second = instr(first+1,"yourtext","-")

   if first > 0 and second > first then
           second = second - first
            result = mid("yourtext",first+1, second-1)
     end if

for "xx-bbb-cc" , result will be "bbb"

for "xx-bbb-cc-zz-cc, result will be "bbb"

for xxxx-bbb, there will be no result

Upvotes: 0

Related Questions