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
– William Robertson, Commented Jan 3, 2018 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
– Wiktor Stribiżew, Commented Jan 3, 2018 at 10:24

Gordon Linoff · Accepted Answer · 2018-01-03 10:25:19Z

1

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).

edited Jan 3, 2018 at 10:25

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

answered Jan 3, 2018 at 10:01

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bilal Halayqa Over a year ago

Doesn't work if the IP is at the end without ' ; ' , try the second string

Wiktor Stribiżew Over a year ago

@BilalHalayqa That is because your pattern did not account for that. Remove ; at the end, and it will.

Collectives™ on Stack Overflow

Oracle regexp_substr - extract substring from string comes in different formats

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related