Tools you can use

Hacking Oracle Outer Join

2009-09-23T14:23:00.000-07:00

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

2008-09-24T20:25:00.001-07:00

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

2007-07-25T06:58:00.000-07:00

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