MYSQL REGEXP_SUBSTR: get string or ip out of text

Question

I struggle through a complex query and need help with a REGEXP_SUBSTR command being used in the WHERE CLAUSE: In deed this are two questions.

given three possible records, I want to get the part between the brackets, but only if they match something that looks like an ip. In one case the question is simply between brackets like "[192.168.178.21]"
other case is the text is like "sender=<some192-168-178-12.example.here>"

Case 1:

SELECT REGEXP_SUBSTR('This is a sample of [192.168.178.12] containing relevant data','(?<=[ ]\[)[^\]]#]+') AS sender
SELECT REGEXP_SUBSTR('This is a sample of [dyn-192-168-178-12.example.com] containing relevant data','(?<=[ ]\[)[^\]]#]+') AS sender
SELECT REGEXP_SUBSTR('This is a sample of [only.example com] containing relevant data','(?<=[ ]\[)[^\]]#]+') AS sender

The problem above is that escaping does not work the way I've expected. Expected would be:

127.0.0.1
dyn.127.0.0.1.example.com
NULL

Case 2:

If I search in the field using the WHERE CLAUSE I have limited success with

WHERE ( sndtext RLIKE ' sender=\\<.*[0-9]{1,3}(.|-|_)[0-9]{1,3}(.|-|_)[0-9]{1,3}(.|-|_)[0-9]{1,3}.*\\>')

but the match is too broad, it needs to stop at the '>'

How can I achieve both solution in a WHERE CLAUSE?

score 0 · Accepted Answer · answered May 05 '22 at 16:34

Might not be perfekt but my solution was (less escaping):

SELECT 
   REGEXP_SUBSTR(msgtext, '(?<=[[])[^]]+') AS ip,
   REGEXP_SUBSTR(msgtext,'(?<=[ ]sndtext=<)[^>]+') AS txt
FROM txtmessage m
   WHERE msgtext RLIKE ' sndtext=<.*([0-9]{1,3}[[:punct:]]){3}[0-9]{1,3}[^>]'

score 0 · Answer 2 · answered Jun 06 '22 at 15:13

This works in the MySQL online tester if I'm understanding what you are looking for correctly.

with tbl(id, data) as (
  select 1, 'This is a sample of [192.168.178.12] containing relevant data' from dual union all
  select 2, 'This is a sample of [dyn-192-168-178-12.example.com] containing relevant data' from dual union all
  select 3, 'This is a sample of [only.example com] containing relevant data' from dual union all
  select 4, 'sender=<some192-168-178-12.example.here>' from dual
)
select id, data 
from tbl
where ( data RLIKE '^.*(\\[|\\<).*[0-9]{1,3}(\\.|\\-)[0-9]{1,3}(\\.|\\-)[0-9]{1,3}(\\.|\\-)[0-9]{1,3}.*(\\]|\\>).*$');

MYSQL REGEXP_SUBSTR: get string or ip out of text

2 Answers2