DEV Community

Cover image for SQL Functions: Cleaning Text and Rounding Numbers the Right Way
Navas Herbert
Navas Herbert

Posted on

SQL Functions: Cleaning Text and Rounding Numbers the Right Way

#sql #postgres #beginners #ai

Data arrives messy.

Names in ALL CAPS. Cities spelled differently in different rows. Exam marks with five decimal places nobody asked for. Teachers listed as "Mr. Njoroge" when the report needs "Prof. Njoroge". First and last names in separate columns when the output needs a single full name.

Every analyst has sat with a spreadsheet full of this kind of thing and spent hours fixing it manually. Today we taught the class how to fix it in SQL - once, in a query, automatically, for every row in the table.

Two categories. String functions for text. Number functions for numeric values. One dataset: Greenwood Academy - students, subjects, teachers, exam results.

Part 1: String Functions

UPPER() and LOWER() - Forcing a Case

The most basic text transformation. UPPER() converts everything to capitals. LOWER() converts everything to lowercase.

-- All first names in capitals
SELECT
    first_name,
    UPPER(first_name) AS upper_first_name
FROM greenwood_academy.students;
Enter fullscreen mode Exit fullscreen mode 
first_name  | upper_first_name
------------|------------------
Amina       | AMINA
Brian       | BRIAN
Njeri       | NJERI
Enter fullscreen mode Exit fullscreen mode 
-- All last names in lowercase
SELECT
    last_name,
    LOWER(last_name)
FROM greenwood_academy.students;
Enter fullscreen mode Exit fullscreen mode

The practical use case I gave the class: "Imagine you're comparing two datasets - one where names were entered in ALL CAPS and one where they're in Title Case. Before you JOIN them, you run LOWER() on both sides so the comparison is apples to apples. One function call, no manual editing."

INITCAP() - Title Case, Automatically

INITCAP() capitalises the first letter of each word and lowercases everything else. It's the clean-up function for data that arrived in ALL CAPS or all lowercase.

I ran this live, on my own name, before touching the table:

SELECT 'NAVAS HERBERT';
SELECT INITCAP('NAVAS HERBERT');
Enter fullscreen mode Exit fullscreen mode 
NAVAS HERBERT
Navas Herbert
Enter fullscreen mode Exit fullscreen mode

One function. Every word properly capitalised. Then the same thing on the students table - names that arrived in the wrong case, cleaned in a single column transformation.

The distinction between the three:

Function Input Output
UPPER() navas herbert NAVAS HERBERT
LOWER() NAVAS HERBERT navas herbert
INITCAP() NAVAS HERBERT Navas Herbert

For most name-cleaning tasks, INITCAP() is the right call. For case-insensitive comparisons, LOWER() both sides. UPPER() is useful for codes, abbreviations, and labels where everything should shout.

LENGTH() - How Long Is This Text?

LENGTH() counts every character in a string, including spaces.

SELECT
    subject_name,
    LENGTH(subject_name) AS name_length
FROM greenwood_academy.subjects;
Enter fullscreen mode Exit fullscreen mode 
subject_name      | name_length
------------------|------------
Mathematics       | 11
English           | 7
Kiswahili         | 9
Physical Education| 18
Enter fullscreen mode Exit fullscreen mode

Not the most glamorous function, but one of the most useful for data validation. "If a phone number column has LENGTH() of anything other than 10, something was entered wrong. If a national ID has LENGTH() other than 8, flag it. LENGTH() is how you catch formatting errors without reading every row."

SUBSTRING() - Cutting Out Part of a Text

Sometimes you only want a piece of the string. SUBSTRING(text, start, length) cuts from a starting position for a specified number of characters.

SELECT
    first_name,
    SUBSTRING(first_name, 1, 3) AS name_prefix
FROM greenwood_academy.students;
Enter fullscreen mode Exit fullscreen mode 
first_name  | name_prefix
------------|------------
Amina       | Ami
Brian       | Bri
Wanjiku     | Wan
Enter fullscreen mode Exit fullscreen mode

Position 1 is the first character. SUBSTRING(first_name, 1, 3) means: start at position 1, take 3 characters.

Real-world use cases: extracting the year from a date string, pulling a department code from a longer employee ID, trimming a product SKU to its category prefix. Any time you need part of a string consistently, SUBSTRING is the tool.

CONCAT() - Joining Text Together

CONCAT() takes multiple pieces of text and joins them into one. You can pass columns, string literals, spaces - anything.

-- Combining first and last name into a full name
SELECT
    first_name,
    last_name,
    CONCAT(first_name, ' ', last_name) AS full_name
FROM greenwood_academy.students;
Enter fullscreen mode Exit fullscreen mode 
first_name | last_name  | full_name
-----------|------------|------------------
Amina      | Hassan     | Amina Hassan
Brian      | Otieno     | Brian Otieno
Wanjiku    | Kamau      | Wanjiku Kamau
Enter fullscreen mode Exit fullscreen mode

The space in the middle - ' ' - is a string literal passed as an argument. Without it, CONCAT(first_name, last_name) gives you AminaHassan. Always include the separator you actually want.

Then the more expressive version - building readable sentences from columns:

-- Combine subject and teacher into a readable label
SELECT
    CONCAT(subject_name, ' - ', teacher_name) AS subject_teacher
FROM greenwood_academy.subjects;
Enter fullscreen mode Exit fullscreen mode 
subject_teacher
-----------------------------
Mathematics - Mr. Njoroge
English - Ms. Achieng
Kiswahili - Mr. Kamau
Enter fullscreen mode Exit fullscreen mode

And going further:

SELECT
    CONCAT(teacher_name, ' is teaching ', subject_name) AS description
FROM greenwood_academy.subjects;
Enter fullscreen mode Exit fullscreen mode 
description
-----------------------------------------
Mr. Njoroge is teaching Mathematics
Ms. Achieng is teaching English
Mr. Kamau is teaching Kiswahili
Enter fullscreen mode Exit fullscreen mode

One student - Otieno - said: "This is how report generators work, isn't it? They're just CONCATting column values into sentences." Exactly. Every automated email, every generated PDF header, every dashboard label that says "Welcome, Amina" - somewhere upstream, a CONCAT is running.

TRIM() - Removing Rogue Spaces

Data entry is inconsistent. People press the spacebar before or after a value and nobody notices until a comparison fails or a JOIN returns zero rows because 'English' doesn't match ' English '.

TRIM() removes leading and trailing spaces.

SELECT
    '  English   ',
    LENGTH('  English   '),
    TRIM('  English   '),
    LENGTH(TRIM('  English   '))
Enter fullscreen mode Exit fullscreen mode 
?column?      | length | btrim   | length
--------------|--------|---------|-------
  English     |     12 | English |     7
Enter fullscreen mode Exit fullscreen mode

Twelve characters with the spaces. Seven characters without them. Same word - completely different in any comparison or JOIN without TRIM.

I told the class this is the function that fixes the most confusing bugs in beginner SQL. "You write a WHERE clause that should obviously match. It returns nothing. You stare at it for ten minutes. Then you realise the value in the database has a trailing space. TRIM() is the fix. Run it on both sides of any comparison where values came from user input."

REPLACE() - Swapping One Value for Another

REPLACE(string, find, replacement) scans a string and swaps every occurrence of one piece of text for another.

We used it two ways.

Abbreviating city names - combined with CASE WHEN from last week:

SELECT DISTINCT city,
CASE
    WHEN city = 'Eldoret'  THEN 'ELD'
    WHEN city = 'Nakuru'   THEN 'NKR'
    WHEN city = 'Nairobi'  THEN 'NRB'
    WHEN city = 'Kisumu'   THEN 'KSM'
    WHEN city = 'Mombasa'  THEN 'MSA'
    ELSE city
END AS city_short
FROM greenwood_academy.students;
Enter fullscreen mode Exit fullscreen mode 
city     | city_short
---------|----------
Nairobi  | NRB
Mombasa  | MSA
Kisumu   | KSM
Nakuru   | NKR
Eldoret  | ELD
Enter fullscreen mode Exit fullscreen mode

Updating titles across a column:

-- Swap 'Mr' for 'Prof' across all teacher names
SELECT
    teacher_name,
    REPLACE(teacher_name, 'Mr', 'Prof') AS teacher_renamed
FROM greenwood_academy.subjects;
Enter fullscreen mode Exit fullscreen mode 
teacher_name   | teacher_renamed
---------------|----------------
Mr. Njoroge    | Prof. Njoroge
Mr. Kamau      | Prof. Kamau
Enter fullscreen mode Exit fullscreen mode

One thing I flagged immediately: REPLACE() is literal and global - it swaps every occurrence of the search string in the column, not just the first one. If teacher_name contained "Mr. Mwangi Mrima", both Mr occurrences would be replaced. Real data sometimes has these edge cases. Always test on a SELECT before running on an UPDATE.

Part 2: Number Functions

Three functions. One concept: controlling how a number is rounded.

ROUND(), CEIL(), FLOOR() - Three Ways to Handle Decimals

ROUND(value, decimal_places) - rounds to the nearest value at the specified decimal place. Standard rounding: 0.5 and above rounds up, below 0.5 rounds down.

CEIL(value) - always rounds up to the next whole integer, no matter how small the decimal.

FLOOR(value) - always rounds down to the previous whole integer, no matter how large the decimal.

We ran all three on the same data - exam marks divided by 10 to produce a score out of 10:

SELECT
    result_id,
    marks,
    ROUND(marks / 10.0, 1)  AS round_out_of_10,
    CEIL(marks / 10.0)      AS ceil_out_of_10,
    FLOOR(marks / 10.0)     AS floor_out_of_10
FROM greenwood_academy.exam_results;
Enter fullscreen mode Exit fullscreen mode 
result_id | marks | round_out_of_10 | ceil_out_of_10 | floor_out_of_10
----------|-------|-----------------|----------------|----------------
1         | 73    | 7.3             | 8              | 7
2         | 85    | 8.5             | 9              | 8
3         | 67    | 6.7             | 7              | 6
4         | 92    | 9.2             | 10             | 9
5         | 58    | 5.8             | 6              | 5
Enter fullscreen mode Exit fullscreen mode

Reading across a single row: marks = 73. Divided by 10 gives 7.3. ROUND() keeps it at 7.3 (one decimal place). CEIL() takes it up to 8 - always up. FLOOR() takes it down to 7 - always down.

I drew the analogy on the board: "ROUND is how a fair teacher grades. CEIL is the optimistic teacher - when in doubt, give the benefit. FLOOR is the strict teacher - partial credit only counts when you've fully earned the next mark."

The / 10.0 instead of / 10 is worth a callout. In PostgreSQL, dividing an integer by an integer gives an integer - 73 / 10 returns 7, not 7.3. Writing 10.0 forces decimal division. It's a small detail that produces completely wrong results if you miss it.

Real-world use cases:

  • ROUND() - financial totals, averages, percentages in reports
  • CEIL() - billing systems that charge for partial units (you used 2.1 hours, you're billed for 3)
  • FLOOR() - discount tiers (you spent KES 2,950, that's tier 2, not tier 3)

The Full Function Cheat Sheet

String Functions:

Function What it does Example
UPPER(text) All capitals UPPER('amina') → AMINA
LOWER(text) All lowercase LOWER('AMINA') → amina
INITCAP(text) Title case INITCAP('amina hassan') → Amina Hassan
LENGTH(text) Character count LENGTH('English') → 7
SUBSTRING(text, start, len) Extract part of string SUBSTRING('Amina', 1, 3) → Ami
CONCAT(t1, t2, ...) Join strings together CONCAT('Amina', ' ', 'Hassan') → Amina Hassan
TRIM(text) Remove edge spaces TRIM(' English ') → English
REPLACE(text, find, new) Swap text REPLACE('Mr. Njoroge', 'Mr', 'Prof') → Prof. Njoroge

Number Functions:

Function What it does Example
ROUND(n, dp) Round to decimal places ROUND(7.3, 0) → 7
CEIL(n) Always round up CEIL(7.3) → 8
FLOOR(n) Always round down FLOOR(7.9) → 7

Practice Problems

Easy:

-- 1. Show all student names in lowercase
-- 2. How many characters are in each subject name?
-- 3. Extract the first letter of every teacher's name using SUBSTRING
Enter fullscreen mode Exit fullscreen mode

Medium:

-- Build a student directory label:
-- Format: "Hassan, Amina - Nairobi"
-- (last_name, first_name - city)

SELECT
    CONCAT(last_name, ', ', first_name, ' - ', city) AS directory_label
FROM greenwood_academy.students;
Enter fullscreen mode Exit fullscreen mode

Challenge:

-- Greenwood Academy wants a clean results summary:
-- Show: full name (CONCAT), their marks, score out of 10 (ROUND to 1dp),
-- and a 'grade_label' using CASE WHEN:
--   9–10 → 'Distinction'
--   7–8.9 → 'Merit'
--   5–6.9 → 'Pass'
--   below 5 → 'Fail'
-- You'll need to JOIN students and exam_results
-- Hint: the ROUND score is a decimal - think about how CASE WHEN handles it
Enter fullscreen mode Exit fullscreen mode

What I Noticed Teaching This Session

1. INITCAP on a personal name unlocks it immediately. Running INITCAP('NAVAS HERBERT') live - your own name - before touching the table makes the function feel immediately useful rather than abstract. Every student thought of a dataset they'd used where names were in the wrong case.

2. TRIM explains the bug everyone has had. That moment when a WHERE clause should obviously match but returns nothing - trailing space on one side, clean value on the other. When students recognise that bug from their own experience, TRIM becomes unforgettable.

3. CONCAT into a sentence made CONCAT make sense. CONCAT(teacher_name, ' is teaching ', subject_name) - the output reads like a real sentence, and students immediately connected it to automated reports, email templates, and dashboard labels they'd seen. The function stopped feeling like a SQL trick and started feeling like a tool they'd actually use.

4. Showing ROUND, CEIL, and FLOOR on the same data in the same query is the only way to teach them. Side by side on the same marks, the difference is immediate and visual. Explaining them sequentially with separate examples loses the contrast that makes each one memorable.

What's Next: JOINs

Every function we learned today works on a single table. Next session we connect multiple tables - students, subjects, results - and run these same functions across the combined data.

-- A preview: full name with their subject and rounded score
SELECT
    CONCAT(s.first_name, ' ', s.last_name) AS full_name,
    sub.subject_name,
    ROUND(r.marks / 10.0, 1) AS score_out_of_10
FROM greenwood_academy.students s
JOIN greenwood_academy.exam_results r ON s.student_id = r.student_id
JOIN greenwood_academy.subjects sub ON r.subject_id = sub.subject_id
ORDER BY score_out_of_10 DESC;
Enter fullscreen mode Exit fullscreen mode

Same functions, two JOINs, one clean report. That's next week.

Try It Yourself

Try the challenge problem - the full results summary combining CONCAT, ROUND, and CASE WHEN. That single query uses three sessions of material: functions today, CASE WHEN last week, and GROUP BY from two weeks ago. If it runs cleanly, you're building real SQL intuition.

I'm a data trainer in Nairobi running a full data programme -
Python foundations → Data Science or Data Engineering specialisations.
I write weekly about what we covered, what worked, and what surprised me.

Top comments (1)

Collapse
 
topstar_ai profile image
Luis Cruz •

The use of INITCAP() to standardize names is particularly useful, as it can help prevent inconsistencies in data comparisons. I've found that combining INITCAP() with TRIM() can also help remove any leading or trailing whitespace, further improving data quality. The example you provided, where INITCAP() is used to clean up names like 'NAVAS HERBERT', is a great illustration of its effectiveness. Have you considered exploring other string functions, such as REPLACE() or REGEXP_SUBSTR(), to handle more complex text cleaning tasks?