Site icon IT Tutorial

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

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

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)

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

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

Nested – Nested FUNCTIONS

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

DECODE FUNCTION

CASE EXPRESSION

See you in my next post.

Exit mobile version