SQL PATINDEX equivalent in DAX

Hi smpa01 

there's no such function in M. (nor is there Regex).

But for your pattern, the custom function would look like so:

 

 

 

(SearchText as text, PatIndexString as text) =>

let
   // PatIndexString = "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9]",
   // description = "45678901-12", 
    description = SearchText,
    SplitByBrackets = Text.SplitAny(PatIndexString, "]["),
    CleanUpList = List.Select(SplitByBrackets, each _ <> ""),
    PrepareList = List.Transform(CleanUpList, each if Text.Length(_) > 1 and Text.Contains(_, "-") then "{" & Text.Replace(_, "-", "..") & "}" else _),
    PatIndexStringList = List.Buffer(List.Transform(PrepareList, each try List.Transform(Expression.Evaluate(_), Text.From) otherwise _)),

        LengthPatIndexString = List.Count(PatIndexStringList),

    TextToList = List.Buffer(Text.ToList(description)),
    LG = 
        List.Generate( () => 
        [ Counter = 0, PatIndexCounter = 0],
        each [Counter] <= List.Count(TextToList) and (try [PatIndexCounter] <= List.Count(PatIndexStringList) otherwise false) ,
        each [Contains = 
                if Type.Is(Value.Type(PatIndexStringList{[PatIndexCounter]}), type list) 
                    then  List.AnyTrue(List.Transform(PatIndexStringList{[PatIndexCounter]}, (x) => Text.Contains(TextToList{[Counter]}, x))) 
                    else Text.Contains(TextToList{[Counter]}, PatIndexStringList{[PatIndexCounter]}) 
                         
                ,Counter =  [Counter] + 1
                ,PatIndexCounter = if Contains then [PatIndexCounter] + 1 else 0
 
            ]),

    Custom1 = List.Last(LG),
    Custom3 = if Custom1[PatIndexCounter] = LengthPatIndexString then Text.Range(description, Custom1[Counter] - LengthPatIndexString, Text.Length(description) - ( Custom1[Counter] - LengthPatIndexString) ) else ""
in
    Custom3

 

 

 

It searches for the pattern and grabs the string that matches the pattern in modus "%..%". So the first match found anywhere will be returned.