Show Posts
1561
PSOUG / Re: REGEXP_SUBSTR Function usage
« on: August 10, 2011, 03:15:36 PM »
Hmmm...this would be easy to solve in a second-tier language like PHP, Java, perl, etc, but I'm not sure of the best way to do this in SQL. You might have to do some preprocessing of the string to split it along the delimiter and then examine the values. I'd bet there's a better/cleaner way to do it via SQL but I'm not aware of how that would be done.
Another way might be to use regexp to look for the digits and key off of them. Something like "(/d+)" or "(/d*)" might work.
Another way might be to use regexp to look for the digits and key off of them. Something like "(/d+)" or "(/d*)" might work.
Thanks Mike.
I am aware of the technical differences between empty and null. Sorry for mixing these terms. In this case, Oracle considers thel ack of a substring to be NULL.
If I choose the value 5 (because I want the fifth substring) as shown below, it returns a single row with a null value instead of the value '3301'. As mentioned before, I must use 3 as the occurance to get the fifth substring '3301'. This will become a problem when the third or fourth substrings become populated.
SELECT
REGEXP_SUBSTR( '001.069012...3301', '[^.]+', 1, 5 ) RESULT
FROM
DUAL
;