Replace Text Regex - IP Address Regex

Mikes

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

GrantSmith

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.

MarkSnodgrass

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

GrantSmith

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

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).

GrantSmith

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.

Mikes

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