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
-
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!**0
Answers
-
@GrantSmith is the GOAT of Regex. :) I imagine he will have something for you.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
You rang @MarkSnodgrass ? 😀
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 times199\.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!**2 -
@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).
0 -
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!**0 -
Thanks @GrantSmith. That did the trick. Thank you.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive