Oracle Introduction to SQL Tutorial – 3

Hi everyone, In this article we will examine Functions.

In SQL Language, instead of repeating the same tasks, writing these functions as functions saves you time and makes your queries run more efficiently.

I will go through the example without explaining in detail what the functions mean.  Thus, it will be more understandable to explain by example.

Lower Function

Converts letters in SQL Query to lowercase.

SELECT LOWER(PROD_NAME) FROM SH.PRODUCTS;

Upper Function

Converts letters in SQL Query to uppercase.

SELECT UPPER(PROD_NAME) FROM SH.PRODUCTS;

Initcap Function

Converts only the first letters of the columns in the SQL Query to uppercase.

SELECT Initcap(PROD_NAME) FROM SH.PRODUCTS;

Concat Function

Used to concatenate strings within the SQL Query.

SELECT CONCAT(CUST_FIRST_NAME,CUST_LAST_NAME) FROM SH.CUSTOMERS;

Length Function

Returns the number of characters returned by the SQL Query result.

SELECT CUST_FIRST_NAME, LENGTH(CUST_FIRST_NAME) FROM SH.CUSTOMERS;

Lpad Function

It allows you to add any number of desired characters from left to right in the result of SQL query.

Use of: SELECT LPAD (Column name, <Number to add>, ‘<Characters to add>’) from table_name;

SELECT LPAD(CUST_FIRST_NAME,10,'*') FROM SH.CUSTOMER;

Added ‘*’ from left to right, totaling 10 characters as shown in the picture. You can use the rpad() function to insert from right to left.

Translate Function

As a result of the SQL query written, it converts the desired character in the desired column to the desired character.

Sample usage: SELECT Translate (<column name>, ‘<Character to change>’,'<New character>’ ) from table name;

select cust_first_name from sh.customers;

Now let’s replace the letter ‘A’ with ‘F’.

select translate(cust_first_name,'A','F') from SH.CUSTOMERS;

Oracle Introduction to SQL Tutorial – 2