• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 538
  • Last Modified:

MS Access - Extract a string from a string

I need to extract the state code from the following examples.

MYstring.USA.TX.123332.QA  => return TX

ABESC.TUNUE.TX.AFEEDGF.BEDS = Return TX

The State code will always start after the second occurrence of a period "."  But its position in the string can change.

So i think i need to be able to return the position of the 2nd and 3rd period then with that maybe us MID to extract it.

How do i get the position of those two periods?

Is there a better way?  Maybe SPLIT into array and grab the 3rd value?  I will need other pieces of this string extracted.

Any thoughts?
0
keschuster
Asked:
keschuster
1 Solution
 
keschusterAuthor Commented:
found this - does what i need
http://allenbrowne.com/func-10.html


Function ParseWord(varPhrase As Variant, ByVal iWordNum As Integer, Optional strDelimiter As String = " ", _
    Optional bRemoveLeadingDelimiters As Boolean, Optional bIgnoreDoubleDelimiters As Boolean) As Variant
On Error GoTo Err_Handler
    'Purpose:   Return the iWordNum-th word from a phrase.
    'Return:    The word, or Null if not found.
    'Arguments: varPhrase = the phrase to search.
    '           iWordNum = 1 for first word, 2 for second, ...
    '               Negative values for words form the right: -1 = last word; -2 = second last word, ...
    '               (Entire phrase returned if iWordNum is zero.)
    '           strDelimiter = the separator between words. Defaults to a space.
    '           bRemoveLeadingDelimiters: If True, leading delimiters are stripped.
    '               Otherwise the first word is returned as null.
    '           bIgnoreDoubleDelimiters: If true, double-spaces are treated as one space.
    '               Otherwise the word between spaces is returned as null.
    'Author:    Allen Browne. http://allenbrowne.com. June 2006.
    Dim varArray As Variant     'The phrase is parsed into a variant array.
    Dim strPhrase As String     'varPhrase converted to a string.
    Dim strResult As String     'The result to be returned.
    Dim lngLen As Long          'Length of the string.
    Dim lngLenDelimiter As Long 'Length of the delimiter.
    Dim bCancel As Boolean      'Flag to cancel this operation.

    '*************************************
    'Validate the arguments
    '*************************************
    'Cancel if the phrase (a variant) is error, null, or a zero-length string.
    If IsError(varPhrase) Then
        bCancel = True
    Else
        strPhrase = Nz(varPhrase, vbNullString)
        If strPhrase = vbNullString Then
            bCancel = True
        End If
    End If
    'If word number is zero, return the whole thing and quit processing.
    If iWordNum = 0 And Not bCancel Then
        strResult = strPhrase
        bCancel = True
    End If
    'Delimiter cannot be zero-length.
    If Not bCancel Then
        lngLenDelimiter = Len(strDelimiter)
        If lngLenDelimiter = 0& Then
            bCancel = True
        End If
    End If

    '*************************************
    'Process the string
    '*************************************
    If Not bCancel Then
        strPhrase = varPhrase
        'Remove leading delimiters?
        If bRemoveLeadingDelimiters Then
            strPhrase = Nz(varPhrase, vbNullString)
            Do While Left$(strPhrase, lngLenDelimiter) = strDelimiter
                strPhrase = Mid(strPhrase, lngLenDelimiter + 1&)
            Loop
        End If
        'Ignore doubled-up delimiters?
        If bIgnoreDoubleDelimiters Then
            Do
                lngLen = Len(strPhrase)
                strPhrase = Replace(strPhrase, strDelimiter & strDelimiter, strDelimiter)
            Loop Until Len(strPhrase) = lngLen
        End If
        'Cancel if there's no phrase left to work with
        If Len(strPhrase) = 0& Then
            bCancel = True
        End If
    End If

    '*************************************
    'Parse the word from the string.
    '*************************************
    If Not bCancel Then
        varArray = Split(strPhrase, strDelimiter)
        If UBound(varArray) >= 0 Then
            If iWordNum > 0 Then        'Positive: count words from the left.
                iWordNum = iWordNum - 1         'Adjust for zero-based array.
                If iWordNum <= UBound(varArray) Then
                    strResult = varArray(iWordNum)
                End If
            Else                        'Negative: count words from the right.
                iWordNum = UBound(varArray) + iWordNum + 1
                If iWordNum >= 0 Then
                    strResult = varArray(iWordNum)
                End If
            End If
        End If
    End If

    '*************************************
    'Return the result, or a null if it is a zero-length string.
    '*************************************
    If strResult <> vbNullString Then
        ParseWord = strResult
    Else
        ParseWord = Null
    End If

Exit_Handler:
    Exit Function

Err_Handler:
    Call LogError(Err.Number, Err.Description, "ParseWord()")
    Resume Exit_Handler
End Function

Open in new window

0
 
Patrick MatthewsCommented:
If you can always count on that format, then you can do it with native functions:

SELECT MyColumn, 
    Mid(MyColumn, InStr(InStr(1, MyColumn, ".") + 1, MyColumn, ".") + 1, 2) AS StCode
FROM MyTable

Open in new window

0
 
pteranodon72Commented:
Split("MYstring.USA.TX.123332.QA", ".")(2)
returns "TX"

Split("ABESC.TUNUE.TX.AFEEDGF.BEDS", ".")(2)
returns "TX"

The index 2 gives you the third part of the zero-based array. MYstring and ABESC are at index 0, etc.
0
 
RyanProject Engineer, ElectricalCommented:
If going VBA route, easiest to use Split(str,".")(3) to get 3rd token.
0
 
Patrick MatthewsCommented:
MrBullwinkle,

If going VBA route, easiest to use Split(str,".")(3) to get 3rd token.

No, you wouldn't.  Split always returns a zero-based array, so to get the third token you would use 2 as the index, as pteranodon72 did in http:#a38293135

Patrick
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now