{"id":12412,"date":"2019-12-10T12:18:22","date_gmt":"2019-12-10T12:18:22","guid":{"rendered":"https:\/\/ittutorial.org\/?p=12412"},"modified":"2019-12-14T17:25:21","modified_gmt":"2019-12-14T17:25:21","slug":"oracle-introduction-to-sql-tutorial-3","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/oracle-introduction-to-sql-tutorial-3\/","title":{"rendered":"Oracle Introduction to SQL Tutorial – 3"},"content":{"rendered":"

Hi everyone, In this article we will examine Functions.<\/p>\n

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

I will go through the example without explaining in detail what the functions mean.\u00a0 Thus, it will be more understandable to explain by example.<\/p>\n

 <\/p>\n

Lower Function<\/h3>\n

Converts letters in SQL Query to lowercase.<\/p>\n

SELECT LOWER(PROD_NAME) FROM SH.PRODUCTS;<\/pre>\n
\"\"<\/p>\n
 <\/p>\n
Upper Function<\/h3>\n
Converts letters in SQL Query to uppercase.<\/p>\n
SELECT UPPER(PROD_NAME) FROM SH.PRODUCTS;<\/pre>\n
\"\"<\/p>\n
 <\/p>\n
Initcap Function<\/h3>\n
Converts only the first letters of the columns in the SQL Query to uppercase.<\/p>\n
SELECT Initcap(PROD_NAME) FROM SH.PRODUCTS;<\/pre>\n
\"\"<\/p>\n
 <\/p>\n
Concat Function<\/h3>\n
Used to concatenate strings within the SQL Query.<\/p>\n
SELECT CONCAT(CUST_FIRST_NAME,CUST_LAST_NAME) FROM SH.CUSTOMERS;<\/pre>\n
 <\/p>\n
\"\"<\/p>\n
 <\/p>\n
Length Function<\/h3>\n
Returns the number of characters returned by the SQL Query result.<\/p>\n
SELECT CUST_FIRST_NAME, LENGTH(CUST_FIRST_NAME) FROM SH.CUSTOMERS;<\/pre>\n
\"\"<\/p>\n
 <\/p>\n
Lpad Function<\/h3>\n
It allows you to add any number of desired characters from left to right in the result of SQL query.<\/p>\n
Use of:\u00a0<\/strong>SELECT LPAD (Column name, <Number to add>, ‘<Characters to add>’) from table_name;<\/span><\/p>\n
SELECT LPAD(CUST_FIRST_NAME,10,'*') FROM SH.CUSTOMER;<\/span><\/pre>\n
\"\"<\/p>\n
 <\/p>\n
Added ‘*’ from left to right, totaling 10 characters as shown in the picture. You can use the rpad()<\/strong> function to insert from right to left.<\/p>\n
 <\/p>\n
Translate Function<\/h3>\n
As a result of the SQL query written, it converts the desired character in the desired column to the desired character.<\/p>\n
Sample usage: <\/strong>SELECT Translate (<column name>, ‘<Character to change>’,'<New character>’ ) from table name;<\/p>\n
select cust_first_name from sh.customers;<\/pre>\n
\"\"<\/p>\n
 <\/p>\n
Now let’s replace the letter ‘A’ with ‘F’.<\/p>\n
select translate(cust_first_name,'A','F') from SH.CUSTOMERS;<\/pre>\n
\"\"<\/p>\n
 <\/p>\n
Oracle Introduction to SQL Tutorial – 2<\/a><\/p><\/blockquote>\n