Detailed Calender Query

SELECT TRUNC( sd + rn ) time_id,
TO_CHAR( sd + rn, 'fmDay' ) day_name,
TO_CHAR( sd + rn, 'D' ) day_number_in_week,
TO_CHAR( sd + rn, 'DD' ) day_number_in_month,
TO_CHAR( sd + rn, 'DDD' ) day_number_in_year,
TO_CHAR( sd + rn, 'W' ) calendar_week_number,
( CASE WHEN TO_CHAR( sd + rn, 'D' ) IN ( 1, 2, 3, 4, 5, 6 ) THEN
NEXT_DAY( sd + rn, 'SATURDAY' ) ELSE
( sd + rn ) END ) week_ending_date,
TO_CHAR( sd + rn, 'MM' ) calendar_month_number,
TO_CHAR( LAST_DAY( sd + rn ), 'DD' ) days_in_cal_month,
LAST_DAY( sd + rn ) end_of_cal_month,
TO_CHAR( sd + rn, 'FMMonth' ) calendar_month_name,
( ( CASE WHEN TO_CHAR( sd + rn, 'Q' ) = 1 THEN
TO_DATE( '03/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 2 THEN
TO_DATE( '06/30/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 3 THEN
TO_DATE( '09/30/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 4 THEN
TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
END ) - TRUNC( sd + rn, 'Q' ) + 1 ) days_in_cal_quarter,
TRUNC( sd + rn, 'Q' ) beg_of_cal_quarter,
( CASE WHEN TO_CHAR( sd + rn, 'Q' ) = 1 THEN
TO_DATE( '03/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 2 THEN
TO_DATE( '06/30/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 3 THEN
TO_DATE( '09/30/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 4 THEN
TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
END ) end_of_cal_quarter,
TO_CHAR( sd + rn, 'Q' ) calendar_quarter_number,
TO_CHAR( sd + rn, 'YYYY' ) calendar_year,
( TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
- TRUNC( sd + rn, 'YEAR' ) ) days_in_cal_year,
TRUNC( sd + rn, 'YEAR' ) beg_of_cal_year,
TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' ) end_of_cal_year
FROM ( SELECT TO_DATE( '12/31/2002', 'MM/DD/YYYY' ) sd,
rownum rn FROM dual CONNECT BY level <= 6575 )
/

REGEXP 3

To get decimals points and vicevers


SELECT REGEXP_REPLACE('18.01', '(\d+)\.(\d+)', '\1') FROM dual;

SELECT REGEXP_REPLACE('18.0991', '(\d+)\.(\d+)', '\2') FROM dual;


For Credit-Card Number System

SELECT regexp_replace( :c, '[[:digit:]]{13,16}', 'X' ) r
FROM dual;

To get decimals points

select (18.9009-floor(18.9009))*power(10,length((18.9009-floor(18.9009)))-1) f from dual

or

SELECT REGEXP_REPLACE('18.9009', '(\d+)\.(\d+)', '\2') FROM dual;


Convert a name 'first middle last' into the 'last middle first' format


SELECT REGEXP_REPLACE('Hubert Horatio Hornblower','(.*) (.*) (.*)','\3 \2 \1') "Reformatted Name" FROM dual ;

To remove dollar sign

SELECT REGEXP_REPLACE('$1,234.56','\$',' ') FROM dual;


SELECT REGEXP_REPLACE('This is a test','t.+','XYZ') FROM dual;

SELECT REGEXP_REPLACE('Mississippi', 'si', 'SI', 1, 0, 'i') FROM dual;

ORA-00932: inconsistent datatypes

ORA-00932: inconsistent datatypes: expected - got CLOB
WHY THIS ERROR OCCURRING ?

WHEN WE EXECUTE THIS QUERY IT RESULTS TO AN ERROR (IE : ORA-00932)

SELECT TO_CLOB('DUMMY') FROM DUAL
UNION
SELECT TO_CLOB('DATA') FROM DUAL;

THIS ERROR CAN BE RESOLVED BY USING UNION ALL

EXAMPLE

SELECT TO_CLOB('DUMMY') FROM DUAL
UNION ALL
SELECT TO_CLOB('DATA') FROM DUAL;

REGEXP 2

Contains alphabets

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:alpha:]]')

Contains only alphabets

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '^[[:alpha:]].*[[:alpha:]]$')

Contains enter character

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:cntrl:]]{1}')

OR

SELECT * FROM test WHERE testcol like '%'||chr(13)||'%'
-------------------------
Start with alphabets

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '^[[:alpha:]]')

Ends with alphabets

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:alpha:]]$')

Contains alphabets only with 5 and more characters

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:alpha:]]{5}')

Contains punctuations

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:punct:]]')

Contains space

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:space:]]')



To insert a space between the characters

SELECT testcol, REGEXP_REPLACE(testcol, '(.)', '\1 ') RES FROM test
To insert a hypen between every 4  characters
 SELECT testcol, REGEXP_REPLACE(testcol, '(....)', '\1-') RES FROM test

To find third charactes with 'a'

SELECT testcol FROM test WHERE REGEXP_LIKE(testcol, '^..a.');

OR

SELECT testcol FROM test WHERE testcol LIKE '__a%'

To find the field having continous 3 spaces

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:space:]]{3}');

REGEXP 1

Find the position of try, trying, tried or tries

SELECT REGEXP_INSTR('We are trying to make the subject easier','tr(y(ing)?|(ied)|(ies))') RESULTNUM FROM dual;

To extract numbers

select regexp_substr('Oracle Database 10g is first grid database','[0-9]+') version from dual;


Following query places a space between Oracle its version

select regexp_replace('Oracle10g','([[:alpha:]])([[:digit:]]+.)','\1 \2') from dual;

Displays the starting position of one or more digits.


select regexp_instr('Oracle Database 10g is first grid aware database','[0-9]+') position from dual;

SELECT REGEXP_replace(TO_CHAR(sysdate, 'YYYY'), '^200[5-8]$','0') FROM dual

SELECT REGEXP_replace(TO_CHAR(sysdate, 'YYYY'), '^200[5-9]$','0') FROM dual


Only retreive data which contains digits
SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:digit:]]');


To Remove Special Characters SELECT REGEXP_REPLACE('##$$$123&&!!__!','[^[:alnum:]'' '']', NULL) FROM dual; SELECT translate('##$$$1$$2#3&&!!__!', '[0-9]#$&&!_','[0-9]') FROM dual; SELECT translate('##$$$123&&!!__!', '0#$&&!_','0') FROM dual; FOR MORE REFERENCE OF REGEXP