Messages

1

PSOUG / Re: REGEXP_SUBSTR Function usage

« on: August 10, 2011, 04:26:38 pm »

Sorry about that last post, it's a little ugly. I didn't expect it to expose all of the RTF code.
Here it is again:
 
Check this out:
SELECT REGEXP_SUBSTR( '001.069012...3301', '([^.]*)(.|$)', 1, 5, NULL, 1 ) RESULT
FROM DUAL
;
This only works on 11g.
If you are on 10g, use this:
SELECT RTRIM( REGEXP_SUBSTR( '001.069012...3301', '[^.]*(.|$)', 1, 5 ), '.' ) RESULT
FROM DUAL
;
Found at: http://forums.oracle.com/forums/thread.jspa?messageID=9530500&#9530500
The key (best) response where this code is listed is by Solomon Yakobson on Apr 19, 2011 11:48 AM.

2

PSOUG / Re: REGEXP_SUBSTR Function usage

« on: August 10, 2011, 04:24:11 pm »

Thanks Mike,
I did some more hunting on the web and found the Oracle Forums. (I hope you don't mind me quoting other forums).
 
Here is what I found:
 
Check this out: <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
[COLOR=#NaNNaNNaN]SELECT[/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN]REGEXP_SUBSTR([/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN]'001.069012...3301'[/COLOR][COLOR=#NaNNaNNaN],[/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN]'([^.]*)(.|$)'[/COLOR][COLOR=#NaNNaNNaN],[/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN]1[/COLOR][COLOR=#NaNNaNNaN],[/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN]5[/COLOR][COLOR=#NaNNaNNaN],[/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN]NULL,[/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN]1[/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN])[/COLOR][COLOR=#NaNNaNNaN] RESULT<o:p></o:p>[/COLOR]
[COLOR=#NaNNaNNaN]FROM[/COLOR][COLOR=#NaNNaNNaN] DUAL<o:p></o:p>[/COLOR]
[COLOR=#NaNNaNNaN];[/COLOR][COLOR=#NaNNaNNaN]<o:p></o:p>[/COLOR]
<o:p> </o:p>
This only works on 11g. <o:p></o:p>
<o:p> </o:p>
If you are on 10g, use this:<o:p></o:p>
[COLOR=#NaNNaNNaN]SELECT[/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN]RTRIM([/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN]REGEXP_SUBSTR([/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN]'001.069012...3301'[/COLOR][COLOR=#NaNNaNNaN],[/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN]'[^.]*(.|$)'[/COLOR][COLOR=#NaNNaNNaN],[/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN]1[/COLOR][COLOR=#NaNNaNNaN],[/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN]5[/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN]),[/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN]'.'[/COLOR][COLOR=#NaNNaNNaN] [/COLOR][COLOR=#NaNNaNNaN])[/COLOR][COLOR=#NaNNaNNaN] RESULT<o:p></o:p>[/COLOR]
[COLOR=#NaNNaNNaN]FROM[/COLOR][COLOR=#NaNNaNNaN] DUAL<o:p></o:p>[/COLOR]
[COLOR=#NaNNaNNaN];[/COLOR]<o:p></o:p>
<o:p> </o:p>
Found at: http://forums.oracle.com/forums/thread.jspa?messageID=9530500&#9530500<o:p></o:p>
There are three other sample code responses. Two of them work and are good options.
The key (best) response where this code is listed is by Solomon Yakobson on Apr 19, 2011 11:48 AM. <o:p></o:p>

 
Thanks for the help Mike. You really helped me to think about this issue and hunt for a good solution.
 
I'll be back soon I'm sure!

3

PSOUG / Re: REGEXP_SUBSTR Function usage

« on: August 10, 2011, 02:43:44 pm »

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

;
 
 

4

PSOUG / REGEXP_SUBSTR Function usage

« on: August 10, 2011, 01:32:37 pm »

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! :-)