Hmmm, I'm not totally up on the finer points of REGEXP_SUBSTR, but here are a couple of ideas...
First, what happens when you have empty substrings
- does it throw an error. or...?
Second, you're probably aware of this, but NULL and empty aren't the same thing...you can test for NULL, but an empty string would return "not null" if you tested one.
I am attempting to extract substrings out of a string (ex. '001.069012...3301').
From the sting I wish to treat the period (.) as the delimiter. As such there are 5 substrings in this string with the third and fourth having a null value. I am currently using the following code to do this:
REGEXP_SUBSTR( ALS.CONCATENATED_SEGMENTS, '[^.]+', 1, 1 ) SUBST_1
This code (which I got off of this wesite, Thanks Mike!) works great as long as there are no empty (null) substrings in the string.
Right now in order to get the 5th substring, I am actually using 3 for the occurance arguement.
Any suggestions on how to get this to work so it can handle empty (null) substrings?
Side note: for those of you familiar with MUMPS, this would work like the $PIECE function.
Thank you for a great website Mike. You have lots of great content here! :-)