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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply