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; Envir...
  • 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

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 ()
)