Oracle SQL Tutorial -3 Single Row Functions

Hi ,

In this article , I will tell you what the number functions , date functions , and nested functions are .

LARGE SMALL CHARACTER CONVERSION FUNCTIONS

  • LOWER : If we want to print an expression in lowercase letters in the query ,

  • UPPER : If we want to print an expression in capital letters , we use those expressions .

SELECT UPPER(CUST_FIRST_NAME) FROM SH.CUSTOMERS WHERE CUST_ID=12;

Here’s another example :

SELECT LOWER(LAST_NAME) FROM ADMIN.NEW_TABLE;

In this example , we provide a small typing of the surnames in the table .

blank

NUMBER FUNCTIONS

  • ROUND : Rounding ,

  • MOD : takes mode ,

  • TRUNC : breaks the part after the comma ,

  • SİGN : determines the sign of the number ,

  • FLOOR : Rounding .

In the example below , we first listed the birth dates of people, then rounded off so that you can see the difference .

SELECT CUST_YEAR_OF_BIRTH FROM SH.CUSTOMERS;

blank

Now we run the query we want, and we write next to the column name , whichever step we want to round , if it is decimal , it writes the part after the comma when it is positive,when it is negative (for example: -1) it rounds the part before the comma .

SELECT ROUND(CUST_YEAR_OF_BIRTH, -1) FROM SH.CUSTOMERS;

blank

HISTORY FUNCTIONS

In the example below, we removed 7 days from the date specified in the cust_eff_from column of the person with id 21 and also added 1 hour by simply running the query you see below 🙂

SELECT CUST_FIRST_NAME,CUST_LAST_NAME,CUST_EFF_FROM,CUST_EFF_FROM -7,CUST_EFF_FROM +1/24 FROM SH.CUSTOMERS WHERE CUST_ID=21;

blank

CONVERSION FUNCTIONS

TO_CHAR FUNCTION (DATE)

  • They must be written in single quotes and have case sensitivity .

SELECT DISTINCT CUST_FIRST_NAME,CUST_EFF_FROM,TO_CHAR(CUST_EFF_FROM , 'dd.mm.yyyy')NOKTALI,TO_CHAR(CUST_EFF_FROM , 'DD MONTH YYYY')"AYRI AYRI" , TO_CHAR(CUST_EFF_FROM , 'Ddspth Month YYYY HH:MI:SS')"Inci incili tarih" FROM SH.CUSTOMERS;

blank

TO_NUMBER AND TO_DATE FUNCTIONS

  • TO_NUMBER (char[, ‘format’]) converts the string to the number in the specified format.

  • TO_DATE (char[, ‘format’]) converts the string to the date in the specified format.

SELECT TO_NUMBER('1964'),CUST_YEAR_OF_BIRTH,TO_DATE('19980101','yyyymmdd') FROM SH.CUSTOMERS;

blank

Nested – Nested FUNCTIONS

  • The innermost function works first.

  • If we want to combine the name, surname and the first 6 characters of the people with _US and capitalize:

SELECT DISTINCT CUST_FIRST_NAME,CUST_LAST_NAME,UPPER(CONCAT(SUBSTR(CUST_LAST_NAME,1,6),'_US')) FROM SH.CUSTOMERS;

blank

Another example :

SELECT DISTINCT FIRST_NAME,LAST_NAME,UPPER(CONCAT(SUBSTR (LAST_NAME,1,8) , '_TR')) FROM ADMIN.NEW_TABLE;

blank

NVL FUNCTION

  • Allows a null value to become an actual value .

  • We can use it in date , number , character types .

  • The data types must be compatible .

DECODE FUNCTION

  • It provides the interpretation of the value given as a parameter and generates new value .

  • Solves the process like IF – THEN – ELSE structure .

CASE EXPRESSION

  • It provides new values by commenting on many column values .

See you in my next post.

 95 views last month,  2 views today

About Melike Duran

blank

Check Also

blank

Create Table ( CTAS ) and Data Types, LOB Data Types in Oracle SQL | Oracle SQL Tutorials -27

Hi, I will explain Create Table ( CTAS ) and Data Types in Oracle SQL in …

Leave a Reply