Friday, February 20, 2009

Query - Names in given order

SELECT REGEXP_REPLACE('Muthu Bernandas Savari', '(.*) (.*) (.*)','\2 \3\1') FROM dual

Query - Contain any character that is not a numeric digit.

SELECT ENAME FROM EMP WHERE REGEXP_LIKE(ENAME, '[^[:digit:]]')

Tuesday, February 17, 2009

Second Largest Number

SELECT empno FROM (SELECT e.empno, RANK() over (ORDER BY e.empno DESC) rk FROM emp e ) WHERE rk = 2

Last month first date

select last_day(add_months(sysdate,-2))+1 from dual

To know the current week of the year

SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'IW')) FROM DUAL