Useful String functions in SQL

Select length('String length') str_LENGTH from dual;

STR_LENGTH
----------------------
13


--Replace string with
Select REPLACE('REPLACE String ','REPLACE','Replaced') str_REPLACE from dual;

STR_REPLACE
----------------
Replaced String


--default replaced value will be null if param2 is not passed
Select REPLACE('REPLACE String ','REPLACE') str_REPLACE from dual;

STR_REPLACE
-----------
String

--Print a portion of string
Select SUBSTR('Sub string',1,3) str_LENGTH from dual;

STR_LENGTH
----------
Sub

--INSTR function
Select instr('Find first occurence of a char in the string','a') str_INSTR from dual;

STR_INSTR
----------------------
25

--TO_CHAR function is mostly used with dates to convert them into desired format
Select to_char(sysdate,'dd-Mon-yyyy hh:mi:ss') str_TOCHAR from dual;

STR_TOCHAR
--------------------
01-Feb-2012 10:11:45

Select to_char(sysdate,'dd-Mon-yy') str_TOCHAR from dual;

STR_TOCHAR
----------
01-Feb-12

Select to_char(sysdate,'hh:mi:ss') str_TOCHAR from dual;

STR_TOCHAR
----------
10:11:45

Select to_char(sysdate,'dd-Mon-yyyy hh:mi Day') str_TOCHAR from dual;


STR_TOCHAR
---------------------------
01-Feb-2012 10:11 Wednesday