Regular expressions: splitting with REGEXP_SUBSTR and "null"
in [Oracle Help]
|
From: citte on 22 Apr 2008 06:31 On 29 Feb, 20:11, spamb...(a)milmac.com (Doug Miller) wrote: > In article <248f35ac-1e8c-424a-80cd-1da3edc66...(a)e6g2000prf.googlegroups.com>, Mark D Powell <Mark.Pow...(a)eds.com> wrote: > > > > >On Feb 28, 11:02=A0am, citte <nicola.ame...(a)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!
From: citte on 22 Apr 2008 06:32 On 29 Feb, 20:11, spamb...(a)milmac.com (Doug Miller) wrote: > In article <248f35ac-1e8c-424a-80cd-1da3edc66...(a)e6g2000prf.googlegroups.com>, Mark D Powell <Mark.Pow...(a)eds.com> wrote: > > > > >On Feb 28, 11:02=A0am, citte <nicola.ame...(a)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!
|
Pages: 1 Prev: Using sequences with mutiple partitions Next: Scanning/mining Oracle 7 (v7.2.3) redo log |