0

I need to search a table and return only the IP addresses found within a text field. (Yes, the IP address should have been saved in its own column but this isn't the case unfortunately.)

I'm hoping to group the data by the amount of times the IP address occurs.

Using this SQL,

SELECT * FROM `table` WHERE `column` LIKE '%somevalue%' AND `text_column` REGEXP '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}'

On a column as such,

+----------------------+------------------+------+-----+---------------------+----------------+
| Field                | Type             | Null | Key | Default             | Extra          |
+----------------------+------------------+------+-----+---------------------+----------------+
{snip}
| text_column          | text             | YES  |     | NULL                |                | 
{snip}
+----------------------+------------------+------+-----+---------------------+----------------+

Where text_column can contain entire paragraphs or sentences like,

Hello, my IP address is: 0.0.0.0

How can I return data to only show the IP address contained within text_column such as,

COUNT(*) text
143      127.0.0.1
84       192.168.0.100
2        255.255.255.0

Thanks for your help.

bafromca
  • 1,906
  • 4
  • 26
  • 42
  • MySQL does not have a native regex replacement, which you would need to extract only that string format. Unless it is imperative that this be done entirely in the query, it would be easier to return rows which match the regex and then extract the IP string in your application code, via another regex. See also http://stackoverflow.com/questions/14699056/how-to-match-an-ip-address-in-mysql/14699075#14699075 – Michael Berkowski Feb 25 '14 at 19:02
  • If you must have RegEx in MySQL consider checking the answer for this question, it uses user installed functions: http://stackoverflow.com/questions/1755408/mysql-regex-replace – Dean Taylor Feb 27 '14 at 18:24

0 Answers0