Regex for IP address

For data modelers & consumers Redshift
1

Hi

I am getting some IP address in my page_urlhost column, which is bad data for me, so I want to clean up that data. Can you please provide me some regex for that. I have tried google for same but the regex there are not working for me.

Appreciate your help.

Thanks!

2

I couldn’t get capturing groups to work properly (and repeat) but this should work fine for IPv4 addresses.

select REGEXP_SUBSTR('127.0.0.1', '^[\\d]{1,3}.[\\d]{1,3}.[\\d]{1,3}.[\\d]{1,3}$')

1 Like