Replace Text Regex - IP Address Regex

I need some help.

I'm trying to find an IP address in a text string and pull the IP value out. I was hoping to use ETL 2.0 formulas, but to start was trying String Replace with the REGEX feature.

REGEX FORMULA. (my IPs start with 199.15):

199\.15\.[0-9]{1,3}\.[0-9]{1,3}


REPLACE WITH: (I'm trying to pull the first match)

$1


However, when I try this it errors out. If you could help me, I would really appreciate it.

Thanks,

Mike

Best Answer

  • GrantSmith
    GrantSmith Indiana 🥷
    edited April 4 Answer ✓

    We just need to add another match group () to the regular expression and surround the entire IP address to match the entire IP address.

    CASE WHEN REGEXP_LIKE(`ip_address`, '^.*199\.15\.([0-9]{1,3})\.([0-9]{1,3}).*$') THEN 
      REGEXP_REPLACE(`ip_address`, '^.*199\.15\.([0-9]{1,3})\.([0-9]{1,3}).*$', '$1')
    ELSE
      ''
    END
    

    $1 will now return the entire IP address since it's the first match group it comes across. Apologies as I should have clarified this in my prior post and I misunderstood your request.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    @GrantSmith is the GOAT of Regex. :) I imagine he will have something for you.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Indiana 🥷

    You rang @MarkSnodgrass ? 😀

    @Mikes

    You're close you just need to tell the regex what groupings you wish to capture with parenthesis. Also I prefer the new formula tile as it tends to work a little bit better.

    REGEXP_REPLACE(`ip_address`, '^.*199\.15\.([0-9]{1,3})\.([0-9]{1,3}).*$', '$1')
    

    For others who may come across this post as it seems you understand regex's fairly well:

    ^.* says to start at the beginning and match any character 0 or more times

    199\.15\.
    

    matches the IP prefix filter

    ([0-9]{1,3})
    

    Matches a number (0-9) 1-3 times and stores it in a match group. the first is stored in $1 and the second is stored in $2

    .*$ says to match any character 0 or more times at the end.


    I'm matching the entire string ^.....$ in this case because I only want to pull out the specific value. Domo's regex doesn't have a way to specifically pull out a match so we need to replace the entire string with the value we're looking for. A caveat to this is that it will return the entire string if it doesn't match the regular expression. If you want to return an empty string instead of the full string you could do something like:

    CASE WHEN REGEXP_LIKE(`ip_address`, '^.*199\.15\.([0-9]{1,3})\.([0-9]{1,3}).*$') THEN 
      REGEXP_REPLACE(`ip_address`, '^.*199\.15\.([0-9]{1,3})\.([0-9]{1,3}).*$', '$1')
    ELSE
      ''
    END
    
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Mikes
    Mikes ⚪️

    @MarkSnodgrass and @GrantSmith. Thank you both for your help. I really appreciate it.

    Is there a way to have this Regex string return the full IP address. It appears that it only returns the 3rd portion of the IP address (e.g. for the IP 199.15.200.30, then $1 = 200 and $2 = 30).

  • Mikes
    Mikes ⚪️

    Thanks @GrantSmith. That did the trick. Thank you.