SQL String Functions

String Functions

SQL Functions:-Oracle has provided a lot of functions that are divided in below sub-category
            A) Single row functions   
                1) Numeric Functions
                2) String Functions
                3) Date Functions
                4) Conversion Functions
            B) Multi row/Group/Grouping function

String function:-Below are the listed function under String Functions category.
lower(string),upper(string),initcap(string),length(string),lpad(string,m,'filler'),rpad(string,m,'filler'),
ltrim(string,'trimmer'),rtrim(string,'trimmer'),trim('trimmer' from string),concat(string1,string2),
reverse(string)

SQL>select lower('ABCD') from dual;
SQL>select empno,ename,lower(ename) from emp;
SQL>select upper('abcd') from dual;
SQL>select upper(ename),empno,job,sal from emp;
SQL>select empno,ename,upper(ename) from emp;
SQL>select initcap('abcd') from dual;
SQL>select empno,ename,initcap(ename) from emp;
SQL>select initcap(ename),empno,job,sal from emp;
SQL>select length('This is my book') from dual;
SQL>select ename,length(ename),empno,job,length(job),sal from emp;
SQL>select ename,lpad(ename,15,'$') from emp;
SQL>select ename,rpad(ename,15,'$') from emp;
SQL>SELECT LPAD('Dan Morgan', 25, 'x') FROM DUAL;
SQL>SELECT RPAD('Dan Morgan', 25, 'x') FROM DUAL;
SQL>SELECT RPAD('Dan Morgan', 25) FROM DUAL;
SQL>select ename,rpad(ename,15,'&') from emp;
SQL>select ename,TRIM('&' FROM rpad(ename,15,'&')) from emp;
SQL>select TRIM(' ' FROM '    This is my book       ') from dual;
SQL>select trim('    This is my book       ') from dual;
SQL>select trim('####This is my book####' ) from dual;
SQL>select ltrim('    This is my book') from dual;
SQL>select rtrim('    This is my book       ') from dual;
SQL>select ltrim(rtrim('    This is my book       ')) from dual;
SQL>SELECT '->' || LTRIM('   Dan Morgan   ') || '<- dual="" from="" span="">
SQL>SELECT '->' || LTRIM('xxx Dan Morgan   ') || '<- dual="" from="" span="">
SQL>SELECT '->' || LTRIM('xxxDan Morgan   ', 'x') || '<- dual="" from="" span="">
SQL>SELECT '->' || RTRIM('   Dan Morgan   ') || '<- dual="" from="" span="">
SQL>SELECT '->' || RTRIM('   Dan Morganxxx') || '<- dual="" from="" span="">
SQL>SELECT '->' || RTRIM('   Dan Morganxxx', 'xxx') || '<- dual="" from="" span="">
SQL>SELECT rtrim('***This is my book******','*') FROM DUAL;
SQL>select ltrim(rtrim('***This is my book******','*'),'*') from dual;
SQL>select trim('*' from '***This is my book******') from dual;

SQL>select substr('abcdef',3,6) from dual;
SQL>select substr('abcdef',3) from dual;
SQL>select ename,substr(ename,3,2),length(ename),empno,job,length(job),sal from emp;
SQL>select ename,substr(ename,-4,2),length(ename),empno,job,length(job),sal from emp;
SQL>select ename,substr(ename,3,-3),length(ename),empno,job,length(job),sal from emp;
SQL>select ename,substr(ename,-3),length(ename),empno,job,length(job),sal from emp;
SQL>select ename,substr(ename,-1,3),length(ename),empno,job,length(job),sal from emp;
SQL>select instr('This is my spoon','s',5,3) from dual;
SQL>select instr('This is my spoon i','is',1,3) from dual;


SQL>select 'ABC'||'CDE' FROM DUAL;
SQL>select empno||' '||ename from emp;
SQL>select concat(empno,ename)  z from emp;
SQL>SELECT ENAME,JOB,CONCAT(ENAME,JOB) FROM EMP;
SQL>SELECT CONCAT('Dan ', 'Morgan') FROM DUAL;
SQL>select empno||ename from emp;
SQL>select ename||' '||empno||' '||sal||' '|| deptno from emp;
SQL>select ename||' '||empno||' '||(sal*2)||' '|| deptno from emp;

SQL>SELECT REVERSE('Dan Morgan') FROM DUAL;
SQL>SELECT ENAME,REVERSE(ENAME) FROM EMP;

SQL>SELECT TRANSLATE('comma,delimited,list',  ',' ,  '|') FROM DUAL;
SQL>SELECT replace('comma,delimited,list',  ',' ,  '|') FROM DUAL;
SQL>SELECT TRANSLATE('CAG-TTT-GAC-ACA-TGG-ATC', 'ACGT', 'GATC')  FROM DUAL;
SQL>SELECT replace('CAG-TTT-GAC-ACA-TGG-ATC', 'ACGT', 'GATC')  FROM DUAL;
SQL>SELECT TRANSLATE('So What my hen', 'ah', 'a') FROM DUAL;
SQL>SELECT TRANSLATE('So What my hen', 'h', ' ') FROM DUAL;
SQL>SELECT TRANSLATE('So What my hen', 'ah', 'e') FROM DUAL;
SQL>SELECT TRANSLATE('"Darn double quotes "', 'A"', 'A') FROM DUAL;--Eliminating Double Quotes
SQL>SELECT REPLACE('So What', 't', 'THIS IS BOOK') FROM DUAL;
SQL>SELECT
REPLACE('An idea that is not dangerous is unworthy of being called an idea at all.', 'n idea', ' software program')
TRUTH FROM DUAL;