How can I create a new column with extracted numbers in SQL or ETL

I have a column called 'description' which can contain multiple elements. One of them is 'Order #1234567890' (or other digits). I want to create a new column that just extracts the numbers in SQL or Magic ETL (so that I can do Beast Modes etc. on it afterwards). How can I create this column? Numbers may exist in other elements, so I just want the numbers that are 9 digits or more. Thanks in advance!

Best Answer

  • user027926
    user027926 ⚪️
    Accepted Answer

    I realize now that I can just edit the text in 'Text Formatting' to include just numbers. Thank you for your help!

Answers

  • I would recommend using Magic ETL for this as that would allow you to use regex expressions to find cases where there are 9 or more digits.

    I'm not an expert with regex, but something like this may work:

    ^[-+\/\s]*([0-9][-+\/\s]*){9,}$
    


  • user027926
    user027926 ⚪️
    edited February 15

    Thanks Superman! I've learned a lot, but still learning. Which function in magic ETL would allow me to enter a regex formula? (I don't have advanced options).


    Thanks!

  • GrantSmith
    GrantSmith Indiana 🟤
    edited February 15

    Hi @user027926


    For future reference you can use the the Replace Text tile with a regular expression and replacement variables. You just need to tell the Search Term field that you're using a regular expression.



    Also replacement variables are in the format of $1, $2, etc instead of the traditional \1 or \2 like some other languages prefer.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    FYI, if you're on Magic 2.0 and you find the Formula tile more intuitive, you can build more sophisticated transforms using:

    cc @GrantSmith

  • Thank you all! Really helpful!

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!