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;
