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 .

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;

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 and when it is negative (for example: -1) it rounds the part before the comma.

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

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;

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;

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;

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;

Another example :

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

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.