SQL: Select Character between Multiple Known Strings

Question

I only want to select characters found between parentheses "()" I found code that will select characters between the same string:

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

It works great because when there is null values I do not get null error.

The trouble is I need to select characters between two known strings. I found this code that looks at two strings but I did not know how to write it into the first code to get my desired results:

   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

This is an example of what I need:

Before:                         I need: 
    Issue                       Issue
    ------                      ------
1   (Dog) at the carpet         Dog at the carpet
2                                                 <---Not a null error
3   (Cat) dog                   Cat

Good question. I would accept any solution that would separate () from text without giving me null errors. — user4581436, Apr 18 '15 at 02:43

score 0 · Answer 1 · answered Apr 17 '15 at 22:26

This code will parse your "varPieces(1)" for the text inside the "(" and ")"

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 = GetStuffYouWant_Parse(varPieces(1))
        Else
            varResult = Null
        End If
    End If
    GetStuffYouWant = varResult
End Function

Public Function GetStuffYouWant_Parse(ByVal pInput As String) As String
   dim first as integer
   dim second as integer
   dim result as string

   first = instr(1,pInput ,"(")
   second = instr(first+1,pInput,")")

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

   GetStuffYouWant_Parse = result
End Function

This almost works, however I am getting 'ERROR' when the values are null — user4581436, Apr 18 '15 at 02:42

SQL: Select Character between Multiple Known Strings

1 Answers1