TRIM, LPAD, RPAD
Clean whitespace with TRIM/LTRIM/RTRIM and pad fixed-width identifiers with LPAD and RPAD.
TRIM, LPAD, RPAD is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
TRIM: Remove Padding
Strip leading and trailing whitespace by default:
SELECT TRIM(' hello '); -- 'hello'
SELECT LTRIM(' hello '); -- 'hello '
SELECT RTRIM(' hello '); -- ' hello'TRIM with Custom Characters
Strip arbitrary characters:
SELECT TRIM(BOTH '0' FROM '0001230'); -- '123'
SELECT TRIM(LEADING '#' FROM '#hashtag'); -- 'hashtag'Cleaning User Input
Always trim user-submitted strings on insert/update — otherwise ' bob ' and 'bob' are different rows:
INSERT INTO users (full_name) VALUES (TRIM(:input));
-- Or enforce at the database with a trigger / CHECK:
ALTER TABLE users
ADD CONSTRAINT name_not_padded CHECK (full_name = TRIM(full_name));LPAD and RPAD
Pad to a fixed width:
SELECT LPAD('7', 4, '0'); -- '0007' (left-pad)
SELECT RPAD('hi', 5, '.'); -- 'hi...' (right-pad)
SELECT LPAD(id::TEXT, 6, '0') FROM orders; -- '000123'Building Receipt-Style Output
Combine PAD and CONCAT for monospaced reports:
SELECT RPAD(product_name, 20, '.') || LPAD(price::TEXT, 8, '.') FROM products;TRANSLATE: Character-by-Character
Each character in the second arg maps to the same-position char in the third arg:
SELECT TRANSLATE('abc123', 'abc', 'xyz'); -- 'xyz123'
SELECT TRANSLATE(phone, '-() ', ''); -- strip multiple chars at onceSplitting Strings
Split into an array:
SELECT STRING_TO_ARRAY('a,b,c,d', ','); -- {'a','b','c','d'}
SELECT REGEXP_SPLIT_TO_ARRAY('a1 b2 c3', '\s+');Splitting Back into Rows
Use UNNEST to explode an array column into rows:
SELECT id, UNNEST(tags) AS tag FROM posts;Joining Arrays Back into Strings
The inverse of split:
SELECT ARRAY_TO_STRING(ARRAY['a','b','c'], ', '); -- 'a, b, c'REPEAT
Repeat a string N times:
SELECT REPEAT('-', 30); -- 30 dashes
SELECT 'Hello' || REPEAT('!', 5); -- 'Hello!!!!!'Padding and Storage
Don't use CHAR(n) as a way to "look padded" — it stores blank-padded values and surprises everyone. Use VARCHAR/TEXT and pad at SELECT time when you need it for display.
Recap
String housekeeping covered: trim user input on the way in, pad for display on the way out, translate when you need character maps.
Quick Check
You want to format an order ID as a 6-digit zero-padded string (e.g. 000123). Which function?
Frequently asked questions
Is the “TRIM, LPAD, RPAD” lesson free?
Yes — the full text of “TRIM, LPAD, RPAD” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “TRIM, LPAD, RPAD”?
Clean whitespace with TRIM/LTRIM/RTRIM and pad fixed-width identifiers with LPAD and RPAD. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “TRIM, LPAD, RPAD” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.