I need to pull the Customer numbers from this string, where IDCUST is the fieldname and the customer code is in the brackets. How would I be able to pull all the customer numbers from it and store them elsewhere?

(CODECURN = "BDS") AND (SWACTV=1) AND (( IDCUST = "0056100" ) OR ( IDCUST = "0092375" ) OR ( IDCUST = "0109550" ) OR ( IDCUST = "3008000" ) OR ( IDCUST = "3016" ) OR ( IDCUST = "3018" ) OR ( IDCUST = "3020" ) OR ( IDCUST = "3026" ) OR (IDCUST = "4011003" ) OR ( IDCUST = "S00087" ) OR ( IDCUST = "X00348")))

This code is a long string that is in a Memo field in an Access database. I need to be able to pull the customer numbers from a string that is in this format, no matter how many customer numbers are in it. Any ideas?

Regular expressions would help

Sorry couldn't resist. I still don't like VB syntax yetch!

'Import the namespace required
Imports System.Text.RegularExpressions

Module Module1

    Sub Main()
        'The string data I have doubled up the quotes from your post 
        'so they are in memory as if come from your Access DB
        Dim searchString As String = "(CODECURN = ""BDS"") AND (SWACTV=1) AND (( IDCUST = ""0056100"" ) OR ( IDCUST = ""0092375"" ) OR ( IDCUST = ""0109550"" ) OR ( IDCUST = ""3008000"" ) OR ( IDCUST = ""3016"" ) OR ( IDCUST = ""3018"" ) OR ( IDCUST = ""3020"" ) OR ( IDCUST = ""3026"" ) OR (IDCUST = ""4011003"" ) OR ( IDCUST = ""S00087"" ) OR ( IDCUST = ""X00348"")))"

        'we need a match object to catch matches
        Dim match As Match

        'Use the Static Match method of the Regex class
        'to find our first (if any) match
        '"""[A-Za-z]*[0-9]+""" = look for:
        'a " 
        'followed by 0 or more letters [A-Za-z]*
        'followed by 1 or more numbers [0-9]+ 
        'followed by another "
        match = Regex.Match(searchString, """[A-Za-z]*[0-9]+""")

        'Success will be true if there was a match
        'So while there are matches keep looking
        While match.Success
            'The replace is to ditch the "'s
            Console.WriteLine(match.Value.Replace("""", ""))
            match = match.NextMatch()
        End While

        'So the console window doesn't disappear immediatly when debugging
    End Sub

End Module

Wow.... thanks :D I didn't understand Regular Expressions too well when I did them at university and was just starting to go through some good tutorials on the subject (which I plan to continue, just so u don't think I plan on implementing this without understanding how it works!) ... but this has been a real Godsend!!!!!

I'm going to study the Regular Expressions to see if I can figure out how to get the expression to add the extra " to the string, so that I can pull the string directly from the Access data field, plug in the extra quotes to escape out the quote characters and save the properly formatted string in SearchString, because I don't know how many Customer numbers IDCUST will be in any one string.


You lost me why do you need to add "'s in ?

In programming languages it is common to declare strings in double quotes. But, what if you actually need a double quote in the string? you have to double them up so when the code is parsed by the compiler you end up with one set of quotes.

If the quotes exist in the data in the database you don't need to add them in. If they don't exist in the database meaning your OP is inaccurate you just need to change the RegEx to look for the = and/or space preceeding the custid's or something. It's silly to pump things into a string just to pull things out.

I think this is going to turn into one those "I have no idea what you're on about" threads LOL

lol I guess you're right.

I understand what u mean about the "s... so disregard my earlier post, then :) I'm trying to implement your code into my app now

It's been working like a charm!

I want to save the customer #'s that are stored in match.value to a variable without the "s... how to I strip them from the match.value? Currently I'm getting customer numbers that are in this format: "ZR0045". I want to save that number like ZR0045.

That's in the code already. match.Value.Replace("""","") the Value property returns a string, all strings inherit the Replace method. The above code returns the string in Value with any "'s replaced with nothing i.e. removed.

That's what I thought.... but when I use a msgbox on the match.value immediately after the Replace I'm seeing "ZR0045" as the value rather than ZR0045 without the quotes.

Right. Replace *returns* a string with the "s replaced, it doesn't change the string in match.Value (strings are immutable)


Won't work.

You need to either:
Dim custid As String = match.Value.Replace("""","")


If that's not the issue then post your code for me so I can see exactly what you're doing.


ok I'll try that. Details at 11 lol

Thanks Holly!!!!!!!! It worked.... :D

Ok... now to get the rest of my code to work LOL