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;

blank

 

Initcap Function

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

SELECT Initcap(PROD_NAME) FROM SH.PRODUCTS;

blank

 

Concat Function

Used to concatenate strings within the SQL Query.

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

 

blank

 

Length Function

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

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

blank

 

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;

blank

 

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;

blank

 

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

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

blank

 

Oracle Introduction to SQL Tutorial – 2

 97 views last month,  2 views today

About Deniz Parlak

blank
Hi, i’m Security Data Scientist & Data Engineer at My Security Analytics. I have experienced Advance Python, Machine Learning and Big Data tools. Also i worked Oracle Database Administration, Migration and upgrade projects. For your questions parlak.deniss@gmail.com

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