How to Clean Messy Employee Names in Oracle SQL Using TRIM() and LOWER()

How to Clean Messy Employee Names in Oracle SQL Using TRIM() and LOWER()

Have you ever worked with an employee table where names are stored in different formats?

For example, some names are in uppercase, others use mixed case, and a few have unwanted spaces around them.

Consider this sample employee data:

The names themselves are correct, but their formats are inconsistent.

This can become a problem when comparing records, preparing reports, or working with data from multiple sources.

Let's clean these values using Oracle SQL.

The requirement

We want to:

• Remove leading and trailing spaces.
• Convert all names into lowercase.
• Display the cleaned values without updating the original table.

Oracle SQL query

SELECT emp_id,
LOWER(TRIM(employee_name)) AS cleaned_name
FROM employees;

Expected output:

The result is now consistent across all employee records.

Understanding the two functions

Let's look at each function separately.

1. TRIM()

"TRIM()" removes spaces from the beginning and end of a string.

SELECT TRIM(' JOHN ') AS name
FROM dual;

Output:

JOHN

1. LOWER()

"LOWER()" converts alphabetic characters into lowercase.

SELECT LOWER('JOHN') AS name
FROM dual;

Output:

john

1. Combining TRIM() and LOWER()

In our original query:

LOWER(TRIM(employee_name))

Oracle evaluates the inner function first.

• "TRIM()" removes unwanted spaces.
• "LOWER()" converts the remaining text into lowercase.

For example:

' JOHN '
|
TRIM()
|
'JOHN'
|
LOWER()
|
'john'

One important point: this SELECT statement only displays the transformed values. It does not modify the original employee records.

What if there are extra spaces between words?

Here is something worth testing.

SELECT TRIM(' JOHN SMITH ') AS name
FROM dual;

Output:

JOHN SMITH

Notice that the spaces between "JOHN" and "SMITH" are still present.

If you want to display names in title case, Oracle provides another function called "INITCAP()".

SELECT INITCAP(TRIM(employee_name)) AS formatted_name
FROM employees;

For example:

john smith

Becomes:

John Smith

Your turn: Test your Oracle SQL knowledge

Which Oracle SQL function converts:

"john smith"

Into:

"John Smith"

A. "UPPER()"

B. "LOWER()"

C. "INITCAP()"

D. "TRIM()"

What is your answer? Share it in the comments.

A quick reference

Function| What it does
"TRIM()"| Removes leading and trailing spaces
"LOWER()"| Converts text to lowercase
"UPPER()"| Converts text to uppercase
"INITCAP()"| Capitalises the first letter of each word

These functions are simple, but they are useful when cleaning text data in everyday SQL work.

The next time you receive inconsistent employee names, try combining "TRIM()" and "LOWER()" before using the data in your analysis.