VB Regular Expression Help

[versacestl]

Trying to get something like so

Where the following is allowed

0-100.#######

where before the decimal 0 to 100 is allowed and after the decimal always 6 digits of any number.

I was trying something like this, but I am definitely not any good with RegEx

Like ("^([0-9]|[1-9][0-9]|[1-0][0-0][0-0])\.(######)$")

Any help would be great

[versacestl]

I thought I had it, and when i put it through Espresso the expression works just fine, but when using it in the following way in a VBscript in VS2005 SSIS, it doesn't work.

Boolean = Variable Like ("^([0-9]|[1-9][0-9]|[1-1][0-0][0-0])\.[0-9][0-9][0-9][0-9][0-9][0-9]$")

Any thoughts?

[dglienna]

Where Digits is the name of your field, and MyData is your data file:

Select * from MyData Where  Digits Like "^([0-9]|[1-9][0-9]|[1-1][0-0][0-0])\.[0-9][0-9][0-9][0-9][0-9][0-9]$"

[versacestl]

Where Digits is the name of your field, and MyData is your data file:

Select * from MyData Where  Digits Like "^([0-9]|[1-9][0-9]|[1-1][0-0][0-0])\.[0-9][0-9][0-9][0-9][0-9][0-9]$"

I am using a flat file to pull in all the rows and the Variable is the Row.Variable that is being checked to mark the boolean to flag the row if its not a proper format.

The RegEx isn't working because i am getting all the valid rows back, yet the expression works just fine in Espresso and is valid. Is this not a proper expression for VB scripting?

[dglienna]

Here's how I have done it before:

Option Explicit
' Add a reference to Microsoft VBScript Regular Expressions 5.5

Private Sub Form_Load()
' Search all the words in a string
  MsgBox validateEmail("[email protected]")
End Sub

Public Function validateEmail(sEmail As String) As Boolean
    Dim emailExp As RegExp
    Set emailExp = New RegExp
    emailExp.Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$"
    If emailExp.Test(sEmail) = True Then
        validateEmail = True
    Else
        validateEmail = False
    End If
End Function

Private Sub cmdReplace_Click()
    Dim re As RegExp
    Dim result As String
    
    Set re = New RegExp
    re.Global = True
    re.Pattern = ",|\.|(\s+LLC)|(\s+Inc)|(\s+$)"
    result = re.Replace("Joan R. Platt LLC ", "")
    re.Pattern = "\s+"
    Debug.Print re.Replace(result, "|")
    ' "Joan|R|Platt"
End Sub

First, says FALSE, then Validates spaces with |

[Slug]

It's been a while since I've GREP'd. I'm not sure what your row looks like but having ranges defined as [0-0] looks a little strange to me. What about something like:

[/d]?[/d]?[/d].[/d][/d][/d][/d][/d][/d]

that should retrieve anything with 1-3 digits to the left of the decimal and 6 digits to the right. Only the first two digits are optional, the rest are required.

LEN

[versacestl]

It's been a while since I've GREP'd. I'm not sure what your row looks like but having ranges defined as [0-0] looks a little strange to me. What about something like:

[/d]?[/d]?[/d].[/d][/d][/d][/d][/d][/d]

that should retrieve anything with 1-3 digits to the left of the decimal and 6 digits to the right. Only the first two digits are optional, the rest are required.

LEN

I am using this in VS 2005 SSIS VB Scripting.. So, I would assume either way should work. I will give this one a try, and let you know.

[dglienna]

VS2005 or Scripting? Wrong forum anyways, it sounds like

[versacestl]

VS2005 or Scripting? Wrong forum anyways, it sounds like

Integration Services in VS 2005 in uses (Visual Basic) for scripting.

[Slug]

OOPS. I guess I forgot a backslash:

[/d]?[/d]?[/d]\.[/d][/d][/d][/d][/d][/d]

Just out of curiosity: What's the pipe character for in your expression? I know I'm out of date but I've never seen that before.

[versacestl]

OOPS. I guess I forgot a backslash:

[/d]?[/d]?[/d]\.[/d][/d][/d][/d][/d][/d]

Just out of curiosity: What's the pipe character for in your expression? I know I'm out of date but I've never seen that before.

The '|' Character causes the regex engine to match either the part on the left side, or the part on the right side. Can be strung together into a series of options.

[versacestl]

OOPS. I guess I forgot a backslash:

[/d]?[/d]?[/d]\.[/d][/d][/d][/d][/d][/d]

Just out of curiosity: What's the pipe character for in your expression? I know I'm out of date but I've never seen that before.

The expression must be formatted as so

[\d]?[\d]?[\d]\.[\d][\d][\d][\d][\d][\d]

This also allows 0's to pad the first two digits if they are there which I can NOT have.

Example:
004.123456
019.123456

I am going to try this also, but if this doesn't work, i am assuming that VB Scripting in SSIS doesn't like the format for some reason.?

^([0-9]|[1-9][0-9]|100)\.[\d][\d][\d][\d][\d][\d]

[versacestl]

I just realized this is Microsoft Visual Basic .NET not 6.0..
I will post a link to this in that section.

[versacestl]

I just tried

       Dim WeightFactor As New Regex("^([0-9]|[1-9][0-9]|100)\.[\d][\d][\d][\d][\d][\d]")
        validWeightFactor = WeightFactor.IsMatch(Row.WEIGHTFACTOR)

and it seems to work, which the method below does not.

validWeightFactor = Row.WEIGHTFACTOR Like ("^([0-9]|[1-9][0-9]|100)\.[\d][\d][\d][\d][\d][\d]")

[dglienna]

Like has nothing to do with RegExp. Like is for lists and tables