How to filter query using Regular Expression?

Asked May 08 '20 at 03:44

Active May 08 '20 at 03:44

Viewed 195 times

I build regular expression script and already tested on https://regexr.com/ to make sure that I capture the correct data I want to get.

I am using alibaba platform, daas to be exact and perform a query on our table.

> select count(1) from ip_tbl
> where ip_address is not null

Result: 51,000 records

Now I want to use my regular expression on IP address that I have been tested, this has to ensure that I capture all the IP on the said table.

> select count(1) from ip_tbl
> where ip_address regexp '\b(\d{1,3}\.){3}\d{1,3}'

Result: 0 records

I tried different function such as RLIKE and REGEXP but it won't work. Please somebody help me regarding this matter. TIA

asked May 08 '20 at 03:44

JL Delos Reyes

What's the underlying SQL platform? – Nick May 08 '20 at 03:51
@Nick I guess it's MySQL. – Tấn Nguyên May 08 '20 at 04:13
@JLDelosReyes Give more detail about the list of IP Address capturing and ignore-capturing – Tấn Nguyên May 08 '20 at 04:15
If it's MySQL, at the very least you will need to escape the ```\``` i.e. `'\\b(\\d{1,3}\\.){3}\\d{1,3}'` – Nick May 08 '20 at 04:17
I want to capture ipv4 or ipv6. Sample 123.456.789.000 or kind. – JL Delos Reyes May 08 '20 at 04:54
Provide sample data, desired results, and an appropriate database tag. – Gordon Linoff May 08 '20 at 12:30

0 Answers0