Forum Discussion

FP68's avatar
FP68
Helper I
6 months ago
Solved

Regular expression

Hi,


I want to search the chain "Name" in a columns which contains  among others "ApplicationName" ?

 

example: Countries=xx; ApplicationId=nbk;Name=yvalxx; ApplicationName=sauve/netbackup; Environment=prod

 

VAR _Key = "Name="
VAR KeyPos = SEARCH ( _Key, TagText, 1, 0 )
 
 
Thanks a lot
  • FBergamaschi's avatar
    FBergamaschi
    6 months ago

    Hi FP68 

     

    ApplicationName2 =
    VAR TagText = Disques_Global[TAGS]
    VAR _Key = "Name="
    VAR _Key2 = "ApplicationName="
    VAR KeyPos = SEARCH ( _Key, TagText, 1, 0 )
    VAR Key2Pos = SEARCH ( _Key2, TagText, 1, 0 )
    VAR ValueStart = KeyPos + LEN ( _Key )
    VAR SemiPos = SEARCH ( ";", TagText, ValueStart, 0 )
    RETURN
    IF (
        KeyPos = 0,
        BLANK(),
        IF (
            KeyPos <> Key2Pos + 11,
            IF (
                SemiPos = 0,
                MID ( TagText, ValueStart, LEN ( TagText ) - ValueStart + 1 ),
                MID ( TagText, ValueStart, SemiPos - ValueStart )
            )
        )
    )
     

    If this helped, please consider giving kudos and mark as a solution

    @me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI

5 Replies

  • Shravan133's avatar
    Shravan133
    Super User
    6 months ago

    // TagText holds the whole "key=value; key=value; ..." string

     

    VAR CleanText =
        SUBSTITUTE ( SUBSTITUTE ( TagText, "; ", ";" ), " ", "" )   // normalize spaces

     

    // Try to find ;Name= anywhere mid-string
    VAR PosSemicolon = SEARCH ( ";Name=", CleanText, 1, 0 )

     

    // Also allow Name= at the very start
    VAR PosStart = IF ( LEFT ( CleanText, 5 ) = "Name=", 1, 0 )

     

    // True token start: either after ';' or at the beginning
    VAR TokenStart =
        IF ( PosSemicolon > 0, PosSemicolon + 1, PosStart )

     

    RETURN
    IF (
        TokenStart > 0,
        VAR ValueStart = TokenStart + LEN ( "Name=" )
        VAR Remainder  = MID ( CleanText, ValueStart, 32767 )
        VAR Value      = LEFT ( Remainder, SEARCH ( ";", Remainder & ";", 1, 0 ) - 1 )
        RETURN Value,
        BLANK ()
    )

    • FP68's avatar
      FP68
      Helper I
      6 months ago

      Hi,
      The colums can start with Name so ";Name" is not OK

      • FBergamaschi's avatar
        FBergamaschi
        Super User
        6 months ago

        Hi FP68 

         

        ApplicationName2 =
        VAR TagText = Disques_Global[TAGS]
        VAR _Key = "Name="
        VAR _Key2 = "ApplicationName="
        VAR KeyPos = SEARCH ( _Key, TagText, 1, 0 )
        VAR Key2Pos = SEARCH ( _Key2, TagText, 1, 0 )
        VAR ValueStart = KeyPos + LEN ( _Key )
        VAR SemiPos = SEARCH ( ";", TagText, ValueStart, 0 )
        RETURN
        IF (
            KeyPos = 0,
            BLANK(),
            IF (
                KeyPos <> Key2Pos + 11,
                IF (
                    SemiPos = 0,
                    MID ( TagText, ValueStart, LEN ( TagText ) - ValueStart + 1 ),
                    MID ( TagText, ValueStart, SemiPos - ValueStart )
                )
            )
        )
         

        If this helped, please consider giving kudos and mark as a solution

        @me in replies or I'll lose your thread

        Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

        Consider voting this Power BI idea

        Francesco Bergamaschi

        MBA, M.Eng, M.Econ, Professor of BI