 | |
Regular expressions: splitting with REGEXP_SUBSTR and "null"
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: Regular expressions: splitting with REGEXP_SUBSTR and "null" -
04-22-2008
, 05:31 AM
|
In article <248f35ac-1e8c-424a-80cd-1da3edc66... (AT) e6g2000prf (DOT) googlegroups.com>, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote: On Feb 28, 11:02=A0am, citte <nicola.ame... (AT) gmail (DOT) 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. |
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!
#22
| |||
| |||
|
|
In article <248f35ac-1e8c-424a-80cd-1da3edc66... (AT) e6g2000prf (DOT) googlegroups.com>, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote: On Feb 28, 11:02=A0am, citte <nicola.ame... (AT) gmail (DOT) 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. |
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!
#23
| |||
| |||
|
|
In article <248f35ac-1e8c-424a-80cd-1da3edc66... (AT) e6g2000prf (DOT) googlegroups.com>, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote: On Feb 28, 11:02=A0am, citte <nicola.ame... (AT) gmail (DOT) 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. |
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!
#24
| |||
| |||
|
|
In article <248f35ac-1e8c-424a-80cd-1da3edc66... (AT) e6g2000prf (DOT) googlegroups.com>, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote: On Feb 28, 11:02=A0am, citte <nicola.ame... (AT) gmail (DOT) 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. |
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!
#25
| |||
| |||
|
|
In article <248f35ac-1e8c-424a-80cd-1da3edc66... (AT) e6g2000prf (DOT) googlegroups.com>, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote: On Feb 28, 11:02=A0am, citte <nicola.ame... (AT) gmail (DOT) 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. |
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!
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.