Regular expressions in script component

  • sqlfriend

    SSC Guru

    Points: 52460

    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

  • sqlfriend

    SSC Guru

    Points: 52460

    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

  • Bojidar Alexandrov

    Old Hand

    Points: 395

    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

  • sqlfriend

    SSC Guru

    Points: 52460

    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