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.htmlPutting 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!
energetic