regexp_like for pulling specific value out of text string

bcasteel
bcasteel 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

  • michiko
    michiko 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

  • bcasteel
    bcasteel 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 |?

Categories