Regex to identify table names from a field with SQL query in it.

HI All, My data set contains a field to store the SQL query that was execute by certain users and I would need to indentify a way to parse the SQL query text from that column and add the table va...

Need Help

Show and Tell

Tips and Tricks

lbendlin
1 year ago
Power Query does not support RegExp natively. You can run R or Python scripts though.

Be aware that Power Query is case sensitive. Be aware that joins can be done via comma too.

Anonymous

1 year ago

Hi AnjanaPothineni ,
You can try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk7NSU0uUdBScAvy91UISUzKSTVUitWJVgp29XF1DlFI1NPSUUjSQ5FXSFTw9PNzDVLw8vf0g4gZKSQp+PsBVZdk5qZ6uijYAvVAmGDDPP2CXYNCgLpC/GHqNUDSmSk6Col5mbmJOToKRalpRanFGQX5mXklmgpQ6/EpQnVyLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"SQL QUERY" = _t]),
    ExtractTableNames = Table.AddColumn(Source, "Table", each 
        let
            SQL = [SQL QUERY],
            Words = Text.Split(Text.Select(SQL, {"A".."Z", "a".."z", "0".."9", " ", "_"}), " "),
            TableNames = List.Select(Words, each List.Contains({"FROM", "JOIN", "INTO"}, Text.Upper(_))),
            NextWords = List.Transform(TableNames, each Text.AfterDelimiter(SQL, _ & " ")),
            CleanedNames = List.Transform(NextWords, each Text.BeforeDelimiter(_, " ")),
            NonEmptyNames = List.Select(CleanedNames, each _ <> "")
        in
            Text.Combine(NonEmptyNames, ", ")
    )
in
    ExtractTableNames

Final output

Or create a dax

NewColumn = 
VAR Query = 'Table'[SQL QUERY]
VAR FromTable = 
    IF(
        SEARCH("FROM ", Query, 1, 0) > 0,
        MID(Query, SEARCH("FROM ", Query, 1, 0) + 5, FIND(" ", Query & " ", SEARCH("FROM ", Query, 1, 0) + 5) - SEARCH("FROM ", Query, 1, 0) - 5),
        BLANK()
    )
VAR JoinTable = 
    IF(
        SEARCH("JOIN ", Query, 1, 0) > 0,
        MID(Query, SEARCH("JOIN ", Query, 1, 0) + 5, FIND(" ", Query & " ", SEARCH("JOIN ", Query, 1, 0) + 5) - SEARCH("JOIN ", Query, 1, 0) - 5),
        BLANK()
    )
VAR IntoTable = 
    IF(
        SEARCH("INTO ", Query, 1, 0) > 0,
        MID(Query, SEARCH("INTO ", Query, 1, 0) + 5, FIND(" ", Query & " ", SEARCH("INTO ", Query, 1, 0) + 5) - SEARCH("INTO ", Query, 1, 0) - 5),
        BLANK()
    )
RETURN
    CONCATENATE(
        CONCATENATE(
            FromTable,
            IF(NOT(ISBLANK(JoinTable)), ", " & JoinTable, "")
        ),
        IF(NOT(ISBLANK(IntoTable)), ", " & IntoTable, "")
    )

Final output