Oracle regexp_substr - extract substring from string comes in different formats

Question

I have a string that comes in different formats like these:

UserId=1;IP Address=85.154.221.54;Device Type=Chrome57
Device Type=Chrome57;IP Address=85.154.221.54
Device Type=Chrome57

How can I extract the IP Address and return empty string if there is no match ?

I have tried the following, but it return the string itself if there is no match.

select regexp_replace('Error=0;UserId=-1;IP Address=85.154.221.54;Device Type=Chrome57', '.*IP Address=(.+);.*', '\1') from dual;

@WiktorStribiżew this is Oracle so an empty string is null. — William Robertson, Jan 03 '18 at 10:23
@WilliamRobertson Actually, I have seen some questions where OP asked specifically for an "empty string". So, I just wanted to make that clear so as to avoid any further terminological and other "disputes". — Wiktor Stribiżew, Jan 03 '18 at 10:24
Possible duplicate of [Oracle SQL - Get rows with values in IP address format (dotted quad)](https://stackoverflow.com/questions/29511621/oracle-sql-get-rows-with-values-in-ip-address-format-dotted-quad) — Patrick Bacon, Jan 03 '18 at 13:24

score 1 · Accepted Answer · edited Jan 03 '18 at 10:25

You may use REGEXP_SUBSTR with your slightly improved pattern:

select regexp_substr('Error=0;UserId=-1;IP Address=85.154.221.54;Device Type=Chrome57',
                     'IP Address=([0-9.]+)',
                     1, 1, NULL, 1
                    )
from dual

Here,

IP Address= - matches IP Address=
([0-9.]+) - matches and captures into Group 1 one or more digits or/and .

See an online demo. If there is no match, the output will be NULL.

select regexp_substr('Error=0;UserId=-1;IP Address=85.154.221.54;Device Type=Chrome57',
                     'IP Address=([0-9.]+)',
                     1, 1, NULL, 1
                    ) as Result
from dual
-- => 85.154.221.54
--select regexp_substr('Error=0;UserId=-1;IP Address= Device Type=Chrome57', 'IP Address=([0-9.]+)', 1, 1, NULL, 1) as result from dual
-- => NULL

Note that the last 1 argument to REGEXP_SUBSTR function returns the contents of capturing group #1 (text captured with the first parenthesized part of the pattern).

Doesn't work if the IP is at the end without ' ; ' , try the second string — Bilal Halayqa, Jan 03 '18 at 10:03
@BilalHalayqa That is because *your* pattern did not account for that. Remove `;` at the end, and it will. — Wiktor Stribiżew, Jan 03 '18 at 10:04

Oracle regexp_substr - extract substring from string comes in different formats

1 Answers1