Re: Regular expressions: splitting with REGEXP_SUBSTR and "null"

On 29 Feb, 20:11, spamb..._at_milmac.com (Doug Miller) wrote:
> In article <248f35ac-1e8c-424a-80cd-1da3edc66..._at_e6g2000prf.googlegroups.com>, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
> >On Feb 28, 11:02=A0am, citte <nicola.ame..._at_gmail.com> wrote:
> >> Hi,
> >> I have a problem, I found on internet a way to split strings separated
> >> by pipe | (for example) with regexp, something like that:
>
> >> SELECTREGEXP_SUBSTR('One|Two|Three|Four','[^|]+', 1, 3)
> >> FROM dual;
>
> >> which gives the result:
> >> Three
>
> >> the problem comes when I have one of the words separated by the pipe
> >> is actually aNull, so the string becomes
> >> 'One|Two||Four|'
>
> >> the query
>
> >> SELECTREGEXP_SUBSTR('One|Two||Four','[^|]+', 1, 3)
> >> FROM dual;
>
> >> result in:
> >> Four
>
> >> I needed aNull(or empty string).
> >> How is this possible?
>
> >> Thank you in advance!
>
> >Will this help?
>
> >SQL> l
> > 1 selectregexp_substr(replace('one|two||four','||','|NULL|'),
> > 2* '[^|]+',1,3) from dual
> >SQL> /
>
> >REGE
> >----
> >NULL
>
> Doesn't work in a more general case, e.g. attempting to select the fourth
> string from 'one|two|||five' -- the desired result isNULL, but this method
> returns 'five'. Conversely, trying to retrieve the fifth string should return
> 'five' but instead returnsNULL.
>
>
>
> >Also to return an actualNULLvalue
> >SQL> selectregexp_substr(replace('one|two||four','||',''),
> > 2 '[^|]+',1,3) from dual;
>
> >R
> >-
>
> Also doesn't work for more general cases:
> - specifying the second substring should return 'two' but instead returns
> 'twofour'
> - specifying the fourth substring should return 'four' but instead returns anullstring
> - specifying the third substring in 'one|two||four|five' should return anull
> string, but instead returns 'five'
>
> In general, when seeking the i-th substring, if the omitted substring is in
> the n-th position, this method is guaranteed to produce correct results only
> when 0 < i < (n - 1), or when i = n AND there are at most n non-null
> substrings. If there are multiple omitted substrings, correct results may also
> be obtained for other values of i, but only by coincidence.

yes, you're right. maybe something more compact than that proposed by Peter could be:

select
  replace(
    regexp_substr(replace('one|two||four','|','|\'),'[^|]+',1,4)   , '\', ''
  ) REG_RESULT
from dual;

where '\' should be a character that NEVER appears in "field" strings (another one could be chosen)
it's an inelegant solution (or really ugly?), you should be really sure it's never present

I wanted to learn regexp, but I think replace could be replaced (sorry :) with a regular expression...

thank you! Received on Tue Apr 22 2008 - 05:31:52 CDT