Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2007
    Posts
    6

    Unanswered: Finding a string after specific characters with trim

    I am trying to populate new (or existing) fields with text parsed from a large text field. The text field is all one string and there may be numerous spaces within.
    I am trying to pull either the 4 or 5 characters after RC: or ABEND:
    also
    Trying to pull the 'up to' 8 characters after S:

    The problems and knowns:
    - the text string could contain multiple colons
    - the text string could contain long series of spaces
    - the text string may not always contain RC: or ABEND: or S:
    - the text string is variable in length
    - the characters after S: may not always be 8 but will never be more than 8

    Example - Description field:
    The message FAILED with a RC: 0008 AT: 23:44 in from S:TCDNM01 on ....

    Wanted result
    0008 into field RC
    TCDNM01 into field SCHED

    Thanks for any assistance

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'll have some code for you in a few minutes. I have something I think you will like! I'm going to tweak it for your needs.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    It's going to take me a bit longer but I should be done within an hour or two.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Here's something to play with in the mean time.

    Here I'm assuming RC is only numeric [0-9], else change to [A-Z0-9], then that the case is

    important. Should you wish for it to be not case sensitive, uncomment the .IgnoreCase line.
    Code:
    ' in the declaration section
    Private mre                 As Object ' vbscript_regexp_55.regexp
    
    ' then the sub
    Public Sub JustTesting5(ByVal v_strIn As String)
    
        Dim mc                  As Object ' vbscript_regexp_55.matchcollection
        Dim m                   As Object ' vbscript_regexp_55.match
        Dim strAbend            As String
        Dim strRC               As String
        Dim strSched            As String
        Dim strTemp             As String
        
        If (mre Is Nothing) Then
            Set mre = CreateObject("vbscript.regexp")
        End If
        With mre
            .Global = True
            '.IgnoreCase = True
            .Pattern = "(RC: [0-9]{4,5})|(ABEND: [A-Z0-9]{4,5})|(S: [A-Z0-9]{1,8})"
            Set mc = .Execute(v_strIn)
            For Each m In mc
                strTemp = Split(m.Value, ": ")(1)
                Select Case left(m.Value, 1)
                Case "A"
                    strAbend = strTemp
                Case "R"
                    strRC = strTemp
                Case "S"
                    strSched = strTemp
                Case Else
                    ' ouch?
                End Select
            Next m
        End With
        Debug.Print strAbend, strRC, strSched
        
    End Sub
    You could probably use the InStr method, too, then loop to the end of the 4/5/8 letters.
    Roy-Vidar

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    SearchString

    Try this!

    You can enter values into a table which to search for a match on.
    (So if you have RC: sometimes and RC; other times, it will catch these!)

    You can also enter values into a table you want stripped out of the value after the match.
    (So if the value has 23:43 and you want the : stripped out, it will become 2343!)
    (or if the value is 3,2343 and you want it to be 32343 you can enter a comma (",") in the ExcludeCharInValue field)

    You can designate which field to store the match on
    (i.e. store RC: to RC field, store S: or S- in the S Field)

    I basically took a routine I had which I like very much because I can enter the values in a table that I want to match on in the string and I DON'T need to ever touch the code! If for example I get a string which has RC- instead of RC: I can enter RC- in the "Search for matching characters table" (i.e. tblSearchItems), tell it what characters to exclude in the value after the match found string, and where to store the value after RC- (i.e. in the field RC).

    RoyVider, I like your code but you have to get into the code and modify it should there be any changes in the incoming string. It's dependent on certain patterns and doesn't write the gotten values from the string into a table. Let me know if you think mine has problems though as I use this technique quite a bit and I'd like your feedback.
    Attached Files Attached Files
    Last edited by pkstormy; 06-09-07 at 19:09.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    One thing about the code I just discovered...if you are missing the S: or some other match in the string and it's supposed to write to 3 fields and only matches on 2 in the string, it will bomb. It also bombs if the last matched value is at the end of the string. I'll try and fix this.
    Last edited by pkstormy; 06-09-07 at 19:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    pkstormy,

    > I am trying to pull either the 4 or 5 characters after RC: or ABEND:

    and

    > - the characters after S: may not always be 8 but will never be more than 8

    your code picks up everything til the next space, regardless whether it is one or hundred characters. As you can see, that's fairly easy to accmomplish with Regular Expressions, though I probably need a bit more tweaking on the spaces. Wouldn't be much work to make a regexp approach more dynamic either, but I don't believe in writing complete apps in fora like this, only illustrate points.

    BTW - you do know you won't get dynamic cursor out of Jet, don't you? It'll be coerced to static. Check it out with

    debug.print rv.locktype, adopendynamic, adopenstatic
    Roy-Vidar

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Updated code

    Here's an update to take into account a value at the end of the string.

    RoyVidar,

    The things I'm trying to capture are:

    1. What if RC: has a value such as 00093a3 or 00043223433 or <space><space><space><space>00442?
    2. What if RC: equals 002?
    3. What if it's not RC but instead R- or RX:? or ABEND: becomes APEND:
    4. What if the value after ABEND: is changed for example from 21:33 to 21;33 or there are some other formatting changes in the values needing to be grabbed?
    5. What if just RC: is missing or all of them are missing?

    You're right in that I use the space to distinguish where the value ends. This is a weak spot in the code for a string which may come in as: RC: 000832AT:23:45. But as long as there is a space, this will grab the value where it may not necessarily be 4-5 characters. (I think it's a tradeoff but I'd actually prefer to grab all the characters if it happens to be a long number). The app I wrote this for required this.

    I like to use the approach where you don't hard-code the parsing but leave it flexible for any changes in the string to parse. I try to make my code as re-usable as possible. If the incoming string is going to change in the way it's formatted at a later time, I don't like to try and diagnose what needs to be changed in code. I'd rather change the values in a table. That's why I initially wrote this - the incoming string changed several times.

    I had most of this code already written for the other larger application I have - this is only a small part and I just had to tweak it a bit, modify a table and add some documentation. I didn't think this was a complete app (but thanks). I thought I may as well send a sample of how it works though. (I'm glad I did this because I never took into account a value at the end of the string.)

    Yes, I realize jet won't give me a dynamic recordset. I usually work with SQL Server tables in my Access mdb which will give me a dynamic recordset - correct?

    Like I said, I like your code. I just prefer to base the criteria in the code on table values.

    katsen has a good couple of different approaches to utilize.
    Attached Files Attached Files
    Last edited by pkstormy; 06-09-07 at 22:18.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by katsen
    The problems and knowns:
    - the text string could contain multiple colons
    - the text string could contain long series of spaces
    - the text string may not always contain RC: or ABEND: or S:
    - the text string is variable in length
    - the characters after S: may not always be 8 but will never be more than 8

    Example - Description field:
    The message FAILED with a RC: 0008 AT: 23:44 in from S:TCDNM01 on ....

    Wanted result
    0008 into field RC
    TCDNM01 into field SCHED
    Let's break this problem down into steps:
    1. Remove double spaces using the Replace() function
    2. See if RC:, ABEND: and/or S: exists.
    3. For each item that exists in the list find it's position with the Instr() function.
    4. Split the string into it's respective parts using combinations of Left(), Mid() and Right() and the respective positions found in part 3 above.
    5. In each of the above parts find where the correct space is
      E.g: "RC: 0008 AT: 23:44 ..." We want the SECOND occurrence of the space
    6. Remove the prefixes of RC:, S: etc *** This can be done any time after stage 4/5
    7. Trim off (or use replace again) to remove extra spaces
    8. dynamically create your SQL statement (this should be relatively easy once you have produced the above
    9. Voila!

    HTH ~George
    George
    Home | Blog

  10. #10
    Join Date
    Jun 2007
    Posts
    6

    Amazing feedback

    I received an email last night from the forums indicating there were posts. I was so excited only to find the site down for maintenance. I know maintenance is necessary but it was really disappointing knowing I wouldn't get another chance until today.

    I want to thank everyone for their solutions. I will begin trying each of them out against the database this evening. I VERY MUCH appreciate that people take out time to help others with less experience.

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The inet interactive site was down last night from around 10:45 to 11:15 pm. as I also was trying to get on here also but couldn't. I emailed Pat Phelan to see if this is normal or if there was a specific problem.
    Last edited by pkstormy; 06-10-07 at 16:36.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Jun 2007
    Posts
    6

    Small problem encountered

    Paul,
    I tried the sample code you provided and have encountered one problem that is probably easily fixed, but I am unsure how to do it. I hope I had mentioned that I am a novice at most scripting.

    The field could contain more than once instance of the same string I am searching. Therefore, I receive an error because you cannot have multiples with the same primary key. Here is a full example of the string (now that I am at work and not doing it from memory). This is all one field but shows wrapping because of the numerous spaces

    JOB X1130LOD FAILED RC: 00012 AT 02:09 ON PLND. CALD458I FAILED AT 2.09.25 RC: 00012 J: P1130LOD S: TCDCPM1 # 0008206

    Is there a way to say only log the first instance, if it exists? They should always be the same.

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    katsen,

    Yes, I think the routine ends up grabbing the last value and putting that in. So in your example for AT, 2.09.25 (verses 2:09) would be the value grabbed for AT. I'll see if I can make a quick tweak to it but read the comments in the function and try to get a basic concept of how it works.

    One problem might be if your string looked like this...

    "JOB X1130LOD FAILED RC: 00012 AT 02:09 ON PLND at SOME OTHER TEXT...."

    In the example above, "SOME" is the first set of characters after "at" so this might be problematic if your incoming string looks like the string above. Do you have any flexibility on how the incoming string is constructed?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Jun 2007
    Posts
    6
    I think we lost something through these emails. I don't need the items after AT. I only need if it is after RC: or S: or ABEND=. I modified the SearchItems table to remove the AT and replace with ABEND.

    I appreciate your assistance.

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ok...You lost me. Please explain further.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •