How to make first letter uppercase

By using INITCAP function it is possible to make first letter of word UPPERCASE

example

select INITCAP('sample initcap') from dual

result: Sample Initcap

How to find a word with different endings in the string

SELECT

REGEXP_SUBSTR('I have earned 100 dollars today ','earn((ing)|(ed))')

FROM dual;

result : earned

If you change the string

SELECT

REGEXP_SUBSTR('I have been earning 100 dollars today ','earn((ing)|(ed))')

FROM dual;

result: earning

How to add first name first letter to last name name

By using regular expression replace it is possible to add first letter of name to the beginning of the last name.

SELECT LOWER(regexp_replace('Lucky, Edy E', '(.+)(, )([A-Z])(.+)','\3\1', 1, 1))

  FROM DUAL;

Result : elucky

How to find alphabetic characters (a-z, A-Z)

By using '[[:alpha:]]' with regular expressions it is possible to find 
alphabetic characters. By adding {3} at the end we limit the length of matching 
alphabetic characters.


Select * from test_table where REGEXP_LIKE(test_column, '[[:alpha:]]');

Select * from test_table where REGEXP_LIKE(test_column, '[[:alpha:]]{3}');

How to find alphanumeric characters (a-z, A-Z, 0-9)

By using '[[:alnum:]]' with regular expressions it is possible to find 

alphanumeric characters.By adding {3} at the end we limit the length of 

matching alphanumeric characters.

Select * from test_table where REGEXP_LIKE(test_column, '[[:alnum:]]');

Select * from test_table where REGEXP_LIKE(test_column, '[[:alnum:]]{3}');

How to find first character of string

By suing SUBSTR  it is possible to find it.

 select SUBSTR ('testing est for test', 1, 1) from dual

resul:t

How to remove repeated character from string

By using  regular experssion it is possible to remove repeated characters from string

Example

select regexp_replace('Tesstt', '(.)\1+','\1') 
  from dual;

Result: Test

How to find last character in the string

By using SUBSTR function it is possible to find last character in string.

Select SUBSTR('last character', -1) from dual

result: r

How to bring sentence till the second occurrence of mentioned word

Bu using regular expression it is possible to find sentence till the second occurrence of mentioned word.

select 

regexp_replace('Payables A 2204216 22490587 Payables A 15 2648191', '^(.*)Payables.*$', '\1') p_result

from dual

select 
substr('Payables A 2204216 22490587 Payables A 15 22648191',1
,instr('Payables A 2204216 22490587 Payables A 15 22648191', 'Payables',1,2)-1)  NAMES
from dual;

How to find number of words in the string

By using length function it is possible to find number of words in the sentence.  First, we find the length of the string and length of the same string without spaces. The difference between them plus 1 will be the number of words in the sentence.

SELECT LENGTH('Oracle PL SQL') - LENGTH(REPLACE('Oracle PL SQL', ' ', '')) + 1 WordCount

from DUAL

How to find the first day of a month

By using the format string MM to extract the month, and then using the DAY format to return the first day of the month within the date , it is possible to find first day of a month:

select
   to_char(
      trunc(:p_date,'MM'),'DAY'
       )
from dual;

How to find dates between two given dates

By using below query, it is possible to generate dates between to given dates. 

SELECT start_date - 1 + rownum as p_date
FROM all_objects
WHERE start_date - 1 + rownum <= end_date

How to find first week day (Sun, Mon .ect) of the month in Oracle PL SQL

How to find first Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday of the month in Oracle PL SQL.

To do so I used Next_day and Trunc functions of Oracle PL SQL

Select NEXT_DAY( TRUNC(sysdate, 'MM') - 1 , 'Sunday') from dual;

By changing sysdate to any particular date and Sunday to any week days, you will find that months mentioned first week day.