Using Single Row Functions to Customize Output
- SQL Functions
Single Row Functions :
1.Character Manipulation Functions :
LPAD & RPAD —
SELECT LPAD(SAL,7,’$’),RPAD(SAL,7,’$’) FROM EMP
/
SELECT LPAD(SAL,2,’$’),RPAD(SAL,2,’$’) FROM EMP
/
LTRIM & RTRIM —
SELECT LTRIM(‘ Removing LTRIM’),RTRIM(‘Removing RTRIM ‘)
FROM DUAL
/
SELECT LTRIM(ENAME),RTRIM(ENAME)
FROM EMP
/
UPPER & LOWER & INITCAP —
SELECT ENAME,UPPER(ENAME),LOWER(ENAME),INITCAP(ENAME)
FROM EMP
/
— CONCAT —
SELECT CONCAT(ENAME,JOB) FROM EMP
/
SELECT CONCAT(‘Bill’,’Gates’) FROM DUAL
/
SELECT CONCAT(‘Bill’||’ ‘||’Gates’) FROM DUAL
/
— SUBSTR —
— SQL Query to substring a Character from a String starting from the first position and ending with the first character —
SELECT ename,SUBSTR(ename,1,1) FROM
emp
/
— Query to substring a Character from a String starting from the first position till the last character —
SELECT ename,SUBSTR(ename,1) FROM
emp
/
— Query to substring a Character from a String starting from the last position till the first character —
SELECT ename,SUBSTR(ename,-1) FROM
emp
/
— SQL Query to substring a Character from a String starting from the first position and continue till the end of the string —
SELECT ename,SUBSTR(ename,2) FROM
emp
/
— INSTR —
— SQL Query to return the numerical position of a character from a String —
SELECT ename,INSTR(ename,’L’) FROM emp
/
2. Numerical Manipulation Functions
— MAX —
— Finding the Maximum Salary from EMP Table —
SELECT MAX(SAL) FROM EMP
/
— MIN —
— Finding the Minimum Salary from EMP Table —
SELECT MIN(SAL) FROM EMP
/
GREATEST —
— Finding the Greatest Value from the list —
SELECT GREATEST(100,200,300) FROM DUAL
/
SELECT SAL,COMM,GREATEST(SAL,COMM) FROM EMP
/
LEAST —
— Finding the Least Value from the list —
SELECT LEAST(100,200,300) FROM DUAL
/
SELECT SAL,COMM,LEAST(SAL,COMM) FROM EMP
/
Difference between GREATEST and MAX :
GREATEST can accept multiple arguments but MAX can accept only one argument
ROUND —
— Rounding to the nearest decimal point —
SELECT ROUND(12.56) FROM DUAL
/
SELECT ROUND(12.34) FROM DUAL
/
TRUNC —
SELECT TRUNC(12.56) FROM DUAL
/
SELECT ROUND(12.34) FROM DUAL
/
3. Date Manipulation Functions
MONTHS_BETWEEN —
— Display using MONTHS_BETWEEN —
SELECT empno,ename,hiredate,sysdate,MONTHS_BETWEEN(sysdate,hiredate) FROM
emp
/
SELECT EMPNO,ENAME,HIREDATE,’01-JAN-2000′,MONTHS_BETWEEN(’01-JAN-2000′,HIREDATE) FROM EMP
/
SELECT EMPNO,ENAME,HIREDATE,SYSDATE,ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)) FROM EMP
/
SELECT EMPNO,ENAME,HIREDATE,’01-JAN-2000′,ROUND(MONTHS_BETWEEN(’01-JAN-2000′,HIREDATE)) FROM EMP
/
ADD_MONTHS —
— Add or subtract using the ADD_MONTHS —
SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL
/
NEXT_DAY —
— Display the Next Date of the Weekly Character using NEXT_DAY —
SELECT NEXT_DAY(SYSDATE,’WED’) FROM DUAL
/
LAST_DAY —
— Display the Last Date of the Date using LAST_DAY —
SELECT LAST_DAY(’01-JAN-2001′) FROM DUAL
/
SELECT LAST_DAY(’16-FEB-2001′) FROM DUAL
/
4. Datatype Conversion Functions :
TO_CHAR —
Convert a Number to Character or Convert a Date to a Character
TO_CHAR Function to convert a Data into desired Character Format —
SELECT EMPNO,HIREDATE,TO_CHAR(HIREDATE,’DD/MM/YYYY’) “Month Hired”
FROM EMP
/
SELECT EMPNO,HIREDATE,TO_CHAR(HIREDATE,’YYYY’) “Year Hired”
FROM EMP
/
SELECT HIREDATE,TO_CHAR(HIREDATE,’RRRR’)
FROM EMP
/
SELECT EMPNO,TO_CHAR(HIREDATE,’Month’) “Year Hired”
FROM EMP
/
SELECT EMPNO,TO_CHAR(HIREDATE,’MONTH’) “Year Hired”
FROM EMP
/
SELECT EMPNO,TO_CHAR(HIREDATE,’Year’) “Year Hired”
FROM EMP
/
— Example for converting No. to Character —
SELECT
SAL,
TO_CHAR(SAL,’999999′) “Display Width”,
TO_CHAR(SAL,’099999′) “Display leading zeros”,
TO_CHAR(SAL,’$99999′) “Display Floating Dollar Sign”,
TO_CHAR(SAL,’L99999′) “Display Local currency symbol”,
TO_CHAR(SAL,’999,999′)”Display’s Comma”,
TO_CHAR(SAL,’9999.9′) “Decimal Point in”
FROM EMP
/
FM Format Model —
— Using the FM Format Model —
SELECT hiredate,TO_CHAR(hiredate,’Month DD,YYYY’)
FROM emp
/
— The Format Mask Parameter removes the zeros and blanks
SELECT hiredate,TO_CHAR(hiredate,’Month DD,YYYY’),TO_CHAR(hiredate,’FMMonth DD,YYYY’)
FROM emp
/
SELECT TO_CHAR(hiredate,’FMMon Ddth,YYYY’)
FROM emp
/
SELECT TO_CHAR(hiredate,’FMMon DDTH,YYYY’)
FROM emp
/
— Query to sort the Days of the week in order —
SELECT ENAME, hiredate, TO_CHAR((hiredate),’Day’) “Day”
FROM emp
ORDER BY “Day”
/
— The Day will return only name of the day and not the numeric value of the day but D will return the numeric value of the Day —
SELECT ENAME,HIREDATE,TO_CHAR((HIREDATE),’Day’),TO_CHAR((HIREDATE),’D’) “Day”
FROM EMP
ORDER BY “Day”
/
SELECT ename,hiredate,TO_CHAR((hiredate),’Day’),TO_CHAR((hiredate),’D’) “Day”
FROM emp
/
— SQL Query to return the Week Number (First Week,Second Week,Third Week) in a Month from a Date —
TO_CHAR(date, ‘W’) will see any date between 1 and 7 of month as first week.
SELECT TO_CHAR(TO_DATE(SYSDATE,’DD-MON-YYYY’),’W’) FROM
dual
/
SELECT TO_CHAR(TO_DATE(’01-SEP-2015′,’DD-MON-YYYY’),’W’) FROM
dual
/
SELECT TO_CHAR(TO_DATE(’31-AUG-2015′,’DD-MON-YYYY’),’W’) FROM
dual
/
SELECT TO_CHAR(TO_DATE(’08-SEP-2015′,’DD-MON-YYYY’),’W’) FROM
dual
/
— TO_NUMBER —
SELECT TO_NUMBER(’12’) FROM
DUAL
/
TO_DATE —
Convert a Character to a Date using TO_DATE
— Convert a String to a default Date Format using TO_DATE —
SELECT TO_DATE(’01/03/02′,’DD/MM/YY’) FROM DUAL
/
SELECT TO_DATE(‘010302′,’DDMMYY’) FROM DUAL
/
— SQL Query to display the No. of weeks from the Current Date compared to HIREDATE col from EMP Table —
SELECT ENAME,((SYSDATE – HIREDATE) /7) Weeks
FROM EMP
/
SELECT ENAME,ROUND((SYSDATE – HIREDATE) /7) Weeks
FROM EMP
/
Nested Functions
SELECT ENAME,
UPPER(CONCAT(SUBSTR(ENAME,1,7),’_TH’))
FROM EMP
/
SELECT ENAME,
INITCAP(CONCAT(SUBSTR(ENAME,1,3),’_TH’))
FROM EMP
/
— Another Example of Nested Function —
SELECT ENAME,TO_CHAR(NEXT_DAY(ADD_MONTHS(HIREDATE,6),’FRIDAY’),’fmDay,Month DDth,YYYY’)
“Next 6 Month Review”
FROM EMP ORDER BY HIREDATE
/
SELECT INITCAP(ename)||’ next Performance Appraisal meet falls on ‘||INITCAP(TO_CHAR(NEXT_DAY(ADD_MONTHS(HIREDATE,6),’FRIDAY’),’fmDay,Month DDth,YYYY’))
“Next 6 Month Review”
FROM emp ORDER BY HIREDATE
/
SELECT TO_CHAR(SYSDATE,’SYEAR’) “Year B.C”,LTRIM(TO_CHAR(SYSDATE,’FMSYEAR’)) “Year B.C”
FROM DUAL
/
SELECT TO_CHAR(SYSDATE,’DDsp Month YYYY’)
FROM DUAL
/
A Julian date or day number is the number of elapsed days
since the beginning of a cycle of
7,980 years invented by Joseph Scaliger in 1583.
SELECT to_char(to_date(‘&NUM’,’j’),’jsp’) from dual
/
SELECT to_char(to_date(‘1000′,’j’),’jsp’)||’ and ‘||to_char(to_date(’10’,’j’),’jsp’) from dual
/
- NULL Value Functions :
NVL —
NVL Function (Replaces a Value for a NULL Value)
— Query to display a value for a NULL Column —
SELECT COMM “Commission”,NVL(COMM,0) “New Commission”
FROM EMP
/
SELECT COMM “Commission”,NVL(COMM,’No Commission’) “New Commission”
FROM EMP
/
SELECT comm,NVL(TO_CHAR(COMM),’No Commission for this Employee’) “Commission” FROM EMP
/
NVL2 —
NVL2 Function
(If the first expression is NOT NULL then it returns the second expression.
If the first expression is NULL then the it returns the third expression)
SELECT COMM “Commission”,NVL2(COMM,0,100) “New Commission”
FROM EMP
/
— Query to display whether the income of employees is made up of salary + commission or just salary
depending on whether the commission col of employee is null or not.
SELECT ename,sal,comm,NVL2(comm,sal + comm),sal) “Income”
FROM emp
/
— NULLIF —
NULLIF Function
(If both the expressions are equal the function returns NULL value.If they are not equal the function returns the first expression.Oracle does not support NULL as the first expression)
SELECT comm “Commission”,NULLIF(1,0) “New Commission”
FROM emp
/
SELECT comm “Commission”,NULLIF(comm,0) “New Commission”
FROM emp
/
SELECT COMM “Commission”,NULLIF(1,0) “Return First Expression”,NULLIF(1,1) “Return NULL”
FROM EMP
/
SELECT LENGTH(ENAME),LENGTH(JOB),
NULLIF(LENGTH(ENAME),LENGTH(JOB)) AS G
FROM EMP
/
COALESCE —
COALESCE
(Coalesce can take multiple arguments.If the first expression is NOT NULL the function returns that expression otherwise it does a COALESCE of remaining expressions)
(Come together and form one mass or whole)
— Query to display values using COALESCE —
SELECT ENAME,mgr,comm,
COALESCE(mgr,comm,-1) coalesce
FROM EMP
/
SELECT ENAME,comm,
COALESCE(comm,comm,-1) coalesce
FROM EMP
/
SELECT COALESCE(NULL,100,-1) Comm
FROM dual
/
SELECT COALESCE(NULL,NULL,-1) Comm
FROM dual
/
SELECT COALESCE(NULL,NULL,NULL,NULL,NULL,-1) Comm
FROM dual
/
SELECT COALESCE(NULL,NULL,2,NULL,3,NULL,-1) Comm
FROM dual
/
CASE —
— Query to multiply the Salaries of Employees when the JOB belongs to CLERK * 10,MANAGER * 50,SALESMAN * 20 —
SELECT EMPNO,ENAME,JOB,SAL “Original Salary”,
CASE JOB
WHEN ‘CLERK’ THEN SAL * 10
WHEN ‘MANAGER’ THEN SAL * 50
WHEN ‘SALESMAN’ THEN SAL * 20
ELSE SAL
END “Revised Salary”
FROM EMP
/
SELECT EMPNO,ENAME,JOB,SAL “Original Salary”,
CASE JOB
WHEN (SELECT ‘CLERK’ FROM DUAL) THEN SAL * 10
WHEN (SELECT ‘MANAGER’ FROM DUAL) THEN SAL * 50
WHEN (SELECT ‘SALESMAN’ FROM DUAL) THEN SAL * 20
ELSE SAL
END “Revised Salary”
FROM EMP
/
DECODE —
— Query to multiply the Salaries of Employees when the JOB belongs to CLERK * 10,MANAGER * 50,SALESMAN * 20 —
SELECT EMPNO,ENAME,JOB,SAL “Original Salary”,
DECODE(JOB,’CLERK’,SAL * 10,
‘MANAGER’,SAL * 20,
‘SALESMAN’,SAL * 30,SAL) “Revised Salary”
FROM EMP
/
The maximum no.of arguments that you can have in a DECODE Function is 255.
CASE inside a DECODE —
— Query to print ‘High’ if the Salary is greater than 2500 and ‘Low’ if the salary is less than 2000 —
SELECT sal,DECODE(
CASE
WHEN sal < 3000 THEN ‘1’ WHEN sal >= 3000 THEN ‘2’
END ,’1′,’High’,’2′,’Low’) FROM EMP
/
— Difference between CASE and DECODE —
- CASE can evaluate any expression, but DECODE is limited to compare discrete values.
2. CASE is more complex,has more code and less readable.
3. Before version 8.1, the DECODE was the only thing providing IF-THEN-ELSE functionality in Oracle SQL.
4. In version 8.1, Oracle introduced the searched CASE statement,which allowed the use of operators like > and BETWEEN
5. In version 9.0, Oracle introduced the simple CASE statement, that reduces some of the verbosity of the CASE statement, but reduces its power to that of DECODE.
6. Another difference is,CASE is an ANSI standard, where as Decode is proprietary for Oracle.