Interesting SQL and PLSQL Tips and Tricks

This blog covers some interesting SQL and PLSQL Tips and Tricks. I will try to include some interesting functions and plsql solutions to some interesting requirements.

Find Last Day of the Month

SQL Function :- LAST_DAY()
SELECT LAST_DAY(SYSDATE) FROM DUAL

Find First Day of the Current Month

SQL Function:- Combination of LAST_DAY () and ADD_MONTHS ()
SELECT LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1 FROM DUAL

Find First Day of the Previous Month

SQL Function:- Combination of LAST_DAY () and ADD_MONTHS ()
SELECT LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1 FROM DUAL

Find First Day of the Next Month

SQL Function:- Combination of LAST_DAY () and ADD_MONTHS ()
SELECT LAST_DAY(SYSDATE)+1 FROM DUAL

Count number of times a character appears in a string

SQL Function(s):- Combination of LENGTH() and REPLACE ()

SELECT LENGTH(‘THIS IS SO MUCH FUN’) – LENGTH(REPLACE(‘THIS IS SO MUCH FUN’,’ ‘)) FROM DUAL
The above query will return 4, which is the number of “spaces” in the string “THIS IS SO MUCH FUN”.

SELECT LENGTH(‘THIS IS SO MUCH FUN’) – LENGTH(REPLACE(‘THIS IS SO MUCH FUN’,’S’)) FROM DUAL
The above query will return 3, which is the number of times the character “S” appears in the string.

I will add more in the coming days. Feel free to ask any questions that you may have.