Blog Tools
Edit your Blog
Build a Blog
RSS Feed
View Profile
« September 2019 »
S M T W T F S
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30
You are not logged in. Log in
Entries by Topic
All topics  «
Business Intelligent
CRM 89
CRM 90
Custom Interface
Data Mining and Warehousi
EPM 88
EPM 89
EPM 90
FIN 89
FIN 90
HR / HCM 89
HR / HCM 90
Implementation Guide
PeopleSoft Appserver
PeopleSoft Shared Reports
PeopleSoft Weblogic
PeopleTools 8.2x
PeopleTools 8.3x
PeopleTools 8.47
PeopleTools 8.48
PeopleTools 8.4x
Red Paper
Shared Architecture
Tips and Tricks
Group Two
PeopleSoft ePenn Blog
Monday, 21 May 2007
Using Regular Expressions in Oracle 10g
Topic: Tips and Tricks
USING REGULAR EXPRESSIONS IN PSQUERYby Steven Stein 

If you were asked, "I think there are some strange Department ID's in the database.  How can I check that all my Department ID's are 6 digits without any letters?"

 

You might do a query with criteria like this:

 

select setid, deptid  from ps_dept_tbl a where

substr(a.deptid,1,1) not in ('0','1','2','3','4','5','6','7','8','9') or

substr(a.deptid,2,1) not in ('0','1','2','3','4','5','6','7','8','9') or

substr(a.deptid,3,1) not in ('0','1','2','3','4','5','6','7','8','9') or

substr(a.deptid,4,1) not in ('0','1','2','3','4','5','6','7','8','9') or

substr(a.deptid,5,1) not in ('0','1','2','3','4','5','6','7','8','9') or

substr(a.deptid,6,1) not in ('0','1','2','3','4','5','6','7','8','9') or

substr(a.deptid,7,1) not in ('',' ') or

substr(a.deptid,8,1) not in ('',' ') or

substr(a.deptid,9,1) not in ('',' ') or

substr(a.deptid,10,1) not in ('',' ')

 

which might return:

 

SETID DEPTID

----- ----------

US001 ALL_DEPTS

SHARE ALL_DEPTS

SHARE DEPTS

SHARE OTHER

 

There's an easier way using regular expressions:

 

SELECT setid, deptid FROM PS_DEPT_TBL

    WHERE REGEXP_LIKE (DEPTID, '[^[:digit:]]')

 

SETID DEPTID

----- ----------

US001 ALL_DEPTS

SHARE ALL_DEPTS

SHARE DEPTS

SHARE OTHER

  

Regular expressions are discussed in an article by Alice Rischert called Inside Oracle Database 10g:

Writing Better SQL Using Regular Expressions

 http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html 

Putting this expression in PSQuery tool is a bit tricky because it doesn't use an operator on the list of operators, so trick it by putting 'A' = 'A' and REGEXP_LIKE(DEPTID, '[^[:digit:]]').  This is how to do it:

  

Regular expressions have been used forever in other languages (C#, Java, Perl, VB, to name a few) and tools (I use them in Textpad for doing search and replace) for years, so there are plenty of good resources out there to help you learn how to build expressions.  One such tool is RegexBuddy which costs about thirty dollars and is refundable.  Check it out at www.regexbuddy.com

 

Good luck using regular expressions!


Posted by steven.stein at 9:55 AM PDT
Post Comment | View Comments (1) | Permalink
Welcome to PeopleSoft ePenn Blog
Mood:  energetic
Welcome to PeopleSoft ePenn Blog.

Posted by idealpenngroup at 3:05 AM PDT
Updated: Monday, 21 May 2007 3:06 AM PDT
Post Comment | Permalink

Newer | Latest | Older