The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Remove Characters in IP Address
Hello Vertica friends,
Need some help. I have a table with a column that holds IP Addresses. The column datatype is VARCHAR, so users have been putting all sorts of things in that column. For example: 'Unknown', 'p-alive', and lots of other strings.
So what I need is a query that removes all characters and only gives me IP Addresses from this table. I was able to remove some of the junk using LEFT function, but I need something that only retrieves numbers and periods. I have also used WHERE IPAddress LIKE '%Unknown%', but this logic will fail at some point.
Perhaps Vertica has a way of identifying numeric values and dots (periods). That would work
Any help would be appreciated.
0
Comments
I think a REGEXP can help...
Example:
Thank you very much. I will try this out now. You guys are always a great help!!!!!!!!!!!!
No problem! Thanks for the positive feedback
You can also use the INET_ATON() function to explicitly parse IP addresses
select INET_ATON('1.2.3.4') is null, INET_ATON('unknown') is null;
..and then INET_NTOA to convert it back to a clean address.
See https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/String/INET_ATON.htm?TocPath=SQL%20Reference%20Manual|SQL%20Functions|IP%20Conversion%20Functions|_____1
Yeah! The INET_ATON function is cleaner and most likely performs much better than a REGEX!
Nice recommendation @Ben_Vandiver
Example: