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,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.