Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

regular expressions in Oracle 9i

Roger25Aug 11 2010 — edited Aug 11 2010
Hello,

Does oracle 9i support regular expressions?
I need to check if a varchar parameter contains only numbers OR letters, otherwise i should return false.

Thanks.

Comments

vissu
Oracle 9i doesn't support. Oracle 10g supports.
780914
Regular expressions was inroduced with Oracle 10g. Prior to it,u can make use of SUBSTR and INSTR, LIKE and REPLACE functions.
BluShadow
Roger22 wrote:
Hello,

Does oracle 9i support regular expressions?
No. They were introduced in 10g.
678284
Does oracle 9i support regular expressions?
Yes.OWA_PATTERN is supported.
1 person found this helpful
BelMan
Roger22 wrote:
Hello,

Does oracle 9i support regular expressions?
I need to check if a varchar parameter contains only numbers OR letters, otherwise i should return false.

Thanks.
Regular Expressions start with Oracle 10g 10.1.0
:)
Hoek
Regular expressions are from 10g and on

There's a little functionality available though:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2200894550208#PAGETOP
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2200894550208#12369668614608
http://download-west.oracle.com/docs/cd/A97329_01/web.902/a90101/pspatt.htm#1005553
Sven W.
Roger22 wrote:
Hello,

Does oracle 9i support regular expressions?
I need to check if a varchar parameter contains only numbers OR letters, otherwise i should return false.

Thanks.
TRANSLATE is helpful to do such a check.

example 
WITH testdata AS
   (SELECT 'abcdef' txt FROM DUAL UNION ALL
    SELECT '1234567' txt FROM DUAL union all
    SELECT '0' txt FROM DUAL union all
    SELECT '123a4567x00' txt FROM DUAL union all
    SELECT '123.4567,00' txt FROM DUAL 
   )
select txt, 
       translate(txt,'a1234567890','a')  numbers_removed,
       translate(txt,'0abcdefghijklonopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','0')  letters_removed
from testdata;

TXT       	NUMBERS_REMOVED	LETTERS_REMOVED
---------------------------------------
abcdef      	abcdef	
1234567   		1234567
0              		0
123a4567x00	ax	123456700
123.4567,00	.,	123.4567,00
One idea is to check the result for NULL or to compare the length of similiar expressions.
Problems are usually special chars and how you want to handle that.

Edited by: Sven W. on Aug 11, 2010 11:07 AM

Edited by: Sven W. on Aug 11, 2010 11:07 AM
1 person found this helpful
Roger25
Thank you, problem solved!

Edited by: Roger22 on 11.08.2010 12:22
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 8 2010
Added on Aug 11 2010
8 comments
5,166 views