Printable View
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
Any help would be greatCode:Like ("^([0-9]|[1-9][0-9]|[1-0][0-0][0-0])\.(######)$")
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.
Any thoughts?Code: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]$")
Where Digits is the name of your field, and MyData is your data file:
Code: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.Quote:
Originally Posted by dglienna
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?
Here's how I have done it before:
First, says FALSE, then Validates spaces with |Code: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
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.Quote:
Originally Posted by Slug
VS2005 or Scripting? Wrong forum anyways, it sounds like
Integration Services in VS 2005 in uses (Visual Basic) for scripting.Quote:
Originally Posted by dglienna
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.Quote:
Originally Posted by Slug
The expression must be formatted as soQuote:
Originally Posted by Slug
This also allows 0's to pad the first two digits if they are there which I can NOT have.Code:[\d]?[\d]?[\d]\.[\d][\d][\d][\d][\d][\d]
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.?
Code:^([0-9]|[1-9][0-9]|100)\.[\d][\d][\d][\d][\d][\d]
I just realized this is Microsoft Visual Basic .NET not 6.0..
I will post a link to this in that section.
I just tried
and it seems to work, which the method below does not.Code:Dim WeightFactor As New Regex("^([0-9]|[1-9][0-9]|100)\.[\d][\d][\d][\d][\d][\d]")
validWeightFactor = WeightFactor.IsMatch(Row.WEIGHTFACTOR)
Code:validWeightFactor = Row.WEIGHTFACTOR Like ("^([0-9]|[1-9][0-9]|100)\.[\d][\d][\d][\d][\d][\d]")
Like has nothing to do with RegExp. Like is for lists and tables
Out of interest, why hasn't anyone suggested using:
\d{6}$
for the bit after the period (to match 6 numbers)?
Am I missing something?
Like is being used on rows.column coming back from a table. HenceQuote:
Originally Posted by dglienna
Boolean = Row.Column Like ("RegEx")
This is working fine for all the other expressions i am using.