Skip to content

    • Magic ETL

    Magic ETL

    regexp_like for pulling specific value out of text string

    Member
    in Magic ETL

    Hello I need some help pulling out T#### and some T####.### from text string.

    REGEXP_LIKE('text','/T[0-9]+.?[0-9]+/g', 'n')

    I am trying to pull the T# out of the following text string and place them in a different column.

    Data Exfiltration via DNS|T1048.003|T1071.004|T1572

    I have multiple rows with different T#'s associated with them. I would prefer to pull them into separate column but will settle for the same column. All it is giving me is 0 in the column.

    Please help.

    Screenshot 2023-05-16 at 11.31.41 PM.png
    Screenshot 2023-05-16 at 11.37.44 PM.png
    Screenshot 2023-05-16 at 11.40.49 PM.png
    Tagged:

    Comments

    • Contributor
      1. Create regex: REGEXP_replace(sText,'(?!T\d.?\d)[A-Za-z]*', '')
      image.png

      Preview:

      image.png

      2) Split the text into column

      image.png

      Preview:

      image.png

      You may need to do a lil bit of formatting clean up the null Col1-Col4

    • Member

      Thank you so much @michiko

      Works amazing. I can't thank you enough. I have a ton to learn.

      A follow up question what if I Just want the T#### in split column with the | separating them and removing the extra |?

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories