Hacking Oracle Outer Join

My friend wanted to use IN command to check the value from a table used in an outer join but Oracle does not allow the usage of IN command for tables connected by outer join. I used regular expressions to overcome this.
Here is an example below with 2 tables (Main and Sometime):

a. Table Main with 1 column 'ID'
ID
10
20
30

b. Table Sometime with 2 columns, ID and Status
ID STATUS
20 ALIVE
10 DEAD



SELECT m.id , s.status
FROM main m, sometime s
WHERE m.id = s.id (+)
AND REGEXP_LIKE (s.status(+), 'ALIVE|DEAD');

The SQL query above would return the following result:

ID STATUS
20 ALIVE
10 DEAD
30

Regular expression for phone number formatting

SELECT REGEXP_REPLACE(
SUBSTR(
REGEXP_REPLACE (
'1-571 426-8102' -- sample phone number
,'[- ]' -- take out hyphen and spaces
),-10 -- read the last 10 numbers
)
,'([0-9]{3})([0-9]{3})([0-9]{4})'
,'\1-\2-\3' -- arrange in format xxx-xxx-xxxx
) phone
FROM dual;

Tokenizer in Oracle PLSQL

I created a tokenizer for Oracle PL/SQL using the Oracle 10g regexp functions:

To test this,
1. first compile the function below:

create or replace FUNCTION Tokenizer
      (p_string VARCHAR2
      ,p_separators in VARCHAR2)
RETURN dbms_sql.varchar2s IS
   l_token_tbl dbms_sql.varchar2s;
   pattern varchar2(250);
BEGIN

   pattern := '[^(' || p_separators || ')]+' ;

   select regexp_substr(p_string, pattern,1,level) token
      bulk collect into l_token_tbl
      from dual
    where regexp_substr(p_string, pattern,1,level) is not null
    connect by regexp_instr(p_string, pattern,1, level ) > 0;

   RETURN l_token_tbl;
END Tokenizer;
/

2. Then call it using,

Declare
   l_token_tbl dbms_sql.varchar2s; -- table of varchar2
begin
   l_token_tbl := Tokenizer(
                                 'anup.pani @somewhere.com ',
                                ' .@' );
   FOR i IN l_token_tbl.FIRST..l_token_tbl.LAST LOOP
      dbms_output.put_line(l_token_tbl(i));
   END LOOP;
end;
/

3. Result

anup
pani
somewhere
com

Hope you find this useful. Let me know your comments or email me in anuppani@gmail.com