Oracle SQL Tutorial Lesson 4

 Oracle SQL Basic Lesson 4

In this lesson we will cover oracle SQL character functions. 

These function can be used with Oracle SQL and Where clause as well.

There is no impact of these function with original stored data. It can be used for reporting and business logic based on user requirements.

Some functions are as below

• Upper
• Lower
• Initcap
• Trim
• Substr
• Instr
• Concat

Remember that SQL is not case sensitive so you can write the syntax in any case.

Let's see some examples 

SELECT first_name, UPPER(last_name) AS last_name_upper
FROM employees;

This query retrieves employee first names and converts their last names to uppercase letters. The UPPER function ensures consistency in data display, which can be useful for reporting or comparison. Just keep in mind that there is no impact on original stored data when we are using these function. These function can be use within SQL statement as well as WHERE clause 


SELECT email, INITCAP(first_name || ' ' || last_name) AS full_name
FROM employees;

To show data as normal case e.g. first case in Upper and other as lower we can use INITCAP FUNCTION.. This is helpful for formatting names that may be stored in all lowercase or uppercase. In above example we use inicap function and doing concatenation of two columns first_name and last_name from employees table. 


SELECT job_id, TRIM('A' FROM 'AAIT_PROGRAAA') AS trimmed
FROM jobs
WHERE job_id = 'IT_PROG';

The TRIM function removes all leading and trailing 'A' characters from the string 'AAIT_PROGRAAA'. This cleans up the job ID string by stripping unwanted characters that might have been added accidentally. We can input any character which needs to be trim in single quotation at the start of function. If we provide simple trim command with column then it will remove extra spaces from the data. E.g. select trim(first_name) from employees;

SELECT first_name, SUBSTR(email, 1, 3) AS email_prefix,
       INSTR(email, '@') AS at_position
FROM employees;

This extracts the first three characters of the email column using SUBSTR. It also finds the position of the '@' symbol with INSTR, which is useful for parsing or validating email addresses.

To pickup first three characters we can use below query 

Select first_name,substr(first_name,1,3) three_chars from employees;

We can use these character functions with where and order by clause 

See below example

Select first_name,substr(first_name,1,3) three_chars from employees

Order by substr(first_name,1,3);

SELECT first_name, REPLACE(first_name, 'e', '*') AS modified_name
FROM employees
WHERE department_id = 60;

The REPLACE function substitutes every 'e' in the employee's first name with an asterisk '*'. This can be used for censoring, formatting, or data transformation tasks in text columns.


SELECT last_name, LENGTH(last_name) AS name_length
FROM employees
WHERE ROWNUM <= 5;

LENGTH returns the number of characters in the last_name field. This is useful to identify unusually long or short names, which might require validation or special handling.

SELECT CONCAT(first_name, last_name) AS full_name_concat,
       first_name || ' ' || last_name AS full_name_operator
FROM employees
WHERE department_id = 100;

This query shows two ways to join strings: CONCAT directly joins two strings, while the || operator can join multiple strings with spaces. Both produce full names from first and last names.

We can change column title which is called column alias. In above example "full name contact" and "full name operator" are example of column alias.

The alias use for display purpose only in SQL and same can't be used in where clause.

SELECT employee_id,
       LOWER(SUBSTR(first_name, 1, 1) || last_name || '@company.com') AS email_address
FROM employees;

The substr function use to get partial values from columns. We need to pass three parameters, first column name then start position and then end position. 

This query generates a standardized email address by concatenating the first letter of the first name, the full last name, and the domain. The LOWER function ensures the email is all lowercase, which is a common convention.