In one of our SSIS package we have a script component that validate a string of route nubmer.
'Pattern should match Route Number in VisibleRouteID string, allowing for a few minor formatting errors (missing/extra spaces, etc.)
_patternRouteNumber = **"\A\s{0,3}\d{4}"**
_regexRouteNumber = New Regex(_patternRouteNumber, RegexOptions.Compiled)
'Pattern should match expected character to the right of the hyphen in the VisibleRouteID string
_patternRouteSuffix = "\-[TFIOSAELW]\b"
What does "\A\s{0,3}\d{4}" mean?
Currently we have route number pattern like this : 0706-F, 0335-T, 4113-O,8798-L, usually the number is a 4 or 5 digit then a dash, then suffix like TFIO etc.
If we want to change it this pattern to add a letter behind the number, for example, 0706A-F, 0335B-T,
Do i need to make the change to the pattern "\A\s{0,3}\d{4}" ?
If so, what should I should change to?
Thanks
September 29, 2020 at 3:27 am
This is more context of the script code:
Public Overrides Sub PreExecute()
'Sample valid value:
'Eff 20070905 Rev 00
'Pattern should match Route Number in VisibleRouteID string, allowing for a few minor formatting errors (missing/extra spaces, etc.)
_patternRouteNumber = "\A\s{0,3}\d{4}"
_regexRouteNumber = New Regex(_patternRouteNumber, RegexOptions.Compiled)
'Pattern should match expected character to the right of the hyphen in the VisibleRouteID string
_patternRouteSuffix = "\-[TFIOSAELW]\b"
_regexRouteSuffix = New Regex(_patternRouteSuffix, RegexOptions.Compiled)
MyBase.PreExecute()
End Sub
Public Overrides Sub VisibleRouteID_ProcessInputRow(ByVal Row As VisibleRouteIDBuffer)
Try
_prescrubbedSuffix = String.Empty
'parse route number if possible
_match = _regexRouteNumber.Match(Row.VisibleRouteID)
If _match.Success Then
Row.RouteNumber = Trim(_match.Value)
Row.IsCleanRouteNumber = True
Else
Row.RouteNumber_IsNull = True
Row.IsCleanRouteNumber = False
End If
'parse route suffix if possible
_prescrubbedSuffix = Row.VisibleRouteID.ToUpper
_match = _regexRouteSuffix.Match(_prescrubbedSuffix)
If _match.Success Then
Row.RouteSuffix = Right(_match.Value, 1)
Row.IsCleanSuffix = True
Else
Row.RouteSuffix_IsNull = True
Row.IsCleanSuffix = False
End If
Catch ex As Exception
ComponentMetaData.FireError(-1, "", "Error parsing VisibleRouteID:" & ex.Message, "", -1, True)
End Try
End Sub
Hi this site is nice to build and explain RegEx expressions
See your first one. It allows 0-3 spaces from the start of the string and then captures 4 digits.
https://regex101.com/r/uZOIe0/2
Your second expression \-[TFIOSAELW]\b matches the hyphen and the suffix letter TFIO...
About your question to add a letter - do you want it to be included in the route number?
If so then your expression must become like this:
\A\s{0,3}\d{4}\w{0,1}
ie with optional character at the end.
https://regex101.com/r/stOwcf/1
Bojo
October 7, 2020 at 12:25 am
Another place I saw similar of this is in a sql script:
select cast(stu.VisibleID as int) as StudentID
,case when r.VisibleRouteID like '[0-9][0-9][0-9][0-9]-T%' then trex.OriginPointID
when r.VisibleRouteID like '[0-9][0-9][0-9][0-9]-I%' then trex.OriginPointID
when r.VisibleRouteID like '[0-9][0-9][0-9][0-9]-F%' then trex.DestinationPointID
when r.VisibleRouteID like '[0-9][0-9][0-9][0-9]-O%' then trex.DestinationPointID
end as PointID, trex.Daysfrom dbo.TransportationRequest as trex
from dbo.TransportationRequest as trex
join dbo.TransportationSatisfaction as tsat................
Now I need to change it to add a single optional letter behind the 4 digits, how can I do it, and if later we give another option to add 2 optional letters how to do that? the letter can be A-Z or a-z
Thanks,
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy