Oracle split message with regexp_substr

Question

I need to split message:

 500 Oracle Parkway.Redwood Shores.*.=13

Now I have a bit worked solution for Substr1/2/4

  SELECT '500 Oracle Parkway.Redwood Shores.*.=13' string1,
  REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','.[^.]+') 
  "SUBSTR1" ,
  replace(REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','[$.]+
  [^.]+'),'.',null) "SUBSTR2" ,
  REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','[$.]+.[$.]+[^.]') 
  "SUBSTR3" ,
  REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','[^=]+$') 
  "SUBSTR4" 
  FROM DUAL;

However Substr3 contains '='. I'd like to have at least '.*.' or ' * '

Could you please give me a hint how to "exclude" any characters (e.g. '=') in regexp?

Any help is much appreciated!

Thank you

Resolved see SUBSTR3.1

      SELECT
     '500 Oracle Parkway.Redwood Shores.*.=13' string1,
      REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','.[^.]+') 
      "SUBSTR1" ,
      replace(REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','[$.]+
      [^.]+'),'.',null) "SUBSTR2" ,
      REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','[$.]+.[$.]+
      [^.]') "SUBSTR3" ,
      REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','[^.]+',1,3) 
      "SUBSTR3.1" ,
      REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','[^=]+$') 
      "SUBSTR4" 
      FROM DUAL;

You're explicitly including the = (or any character following .*.) with the final part of the pattern, [^.]. So just removing that would give you a quick fix. But it would be helpful to explain more about what you're trying to do, and give more example source strings and the output you're trying to achieve for all of them. substr2 is null in your example. Are you really trying to tokenize the string, and them (maybe) remove the = from the final token? — Alex Poole
– Alex Poole, Commented May 10, 2017 at 9:52

Community · Accepted Answer · 2017-05-23 11:54:53Z

2

With much respect to Alex Poole, the regex of the format '[^.]+' fails if one of the elements of the list is missing. It will silently return incorrent data. Please use this form instead. Note I removed the city from the first example. Try it and you may be surprised:

with t (str) as (
  select '500 Oracle Parkway..*.=13' from dual union 
  select 'One Microsoft Way.Redmond.Washington.=27' from dual
)
select str,
  regexp_substr(str, '(.*?)(\.|$)', 1, 1, NULL, 1) as substr1,
  regexp_substr(str, '(.*?)(\.|$)', 1, 2, NULL, 1) as substr2,
  regexp_substr(str, '(.*?)(\.|$)', 1, 3, NULL, 1) as substr3,
  ltrim(regexp_substr(str, '(.*?)(\.|$)', 1, 4, NULL, 1), '=') as substr4
from t;

See here for more info: Split comma separated values to columns in Oracle

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered May 10, 2017 at 14:01

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

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

Comments

Alex Poole · Accepted Answer · 2017-05-10 10:06:00Z

1

It looks like you're trying to tokenize your source string based on periods, and them (maybe) remove the leading equals sign from the fourth token. The solution you've used for your 'substring3.1' can be used for all of them:

with t (str) as (
  select '500 Oracle Parkway.Redwood Shores.*.=13' from dual
  union all select 'One Microsoft Way.Redmond.Washington.=27' from dual
)
select str,
  regexp_substr(str, '[^.]+', 1, 1) as substr1,
  regexp_substr(str, '[^.]+', 1, 2) as substr2,
  regexp_substr(str, '[^.]+', 1, 3) as substr3,
  ltrim(regexp_substr(str, '[^.]+', 1, 4), '=') as substr4
from t;

STR                                      SUBSTR1              SUBSTR2              SUBSTR3    SUBSTR4
---------------------------------------- -------------------- -------------------- ---------- -------
500 Oracle Parkway.Redwood Shores.*.=13  500 Oracle Parkway   Redwood Shores       *          13     
One Microsoft Way.Redmond.Washington.=27 One Microsoft Way    Redmond              Washington 27

answered May 10, 2017 at 10:06

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

2 Comments

Gary_W Over a year ago

@Alex Poole Sorry Alex. :-) I'm on a one-man mission to get people to stop using the '[^.]+' string parsing regex format ever since I found the error mentioned in my post in a production report. I have since encapsulated the code into a utility function where developers can pass a string and the number of the element they want and the function returns the element, hiding the regex altogether for simplicity.I posted it someplace here. Join me in spreading the word!

Alex Poole Over a year ago

@Gary_W - I remember that being brought up before, but this pattern is so common I forget to look for the safer one (or I'm too lazy if I think it might not matter... seems like it does here). I've bookmarked the answer you linked to now *8-)

Collectives™ on Stack Overflow

Oracle split message with regexp_substr

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related