TRIM LTRIM RTRIM
In this page:
TRIM([BOTH | LEADING | TRAILING] [remove_string FROM] string)
LTRIM(string)
RTRIM(string)Introduction to TRIM
TRIM strips whitespace from both the start and end of a string while leaving any spaces in the middle untouched, which matters when comparing or storing user-typed values that often carry accidental padding.
Example: Introduction to TRIM
SELECT TRIM(' Hello World ') AS trimmed;Using LTRIM
LTRIM removes only leading whitespace from the left edge of a string, keeping trailing spaces intact. It's useful when you specifically want to normalize how a value starts without altering anything after it.
Example: Using LTRIM
SELECT LTRIM(' Hello World ') AS left_trimmed;Using RTRIM
RTRIM mirrors LTRIM but works on the right edge instead, stripping trailing spaces while leaving the front of the string alone. This is common when cleaning fixed-width import data that pads values with trailing blanks.
Example: Using RTRIM
SELECT RTRIM(' Hello World ') AS right_trimmed;Cleaning Specific Characters
TRIM isn't limited to whitespace — passing the LEADING, TRAILING, or BOTH keyword lets you strip a specific character, like removing stray leading zeros or trailing commas from imported text instead of just blank space.
Example: Cleaning Specific Characters
SELECT TRIM(LEADING '0' FROM '000123') AS no_leading_zeros;
SELECT TRIM(TRAILING ',' FROM 'item,item,') AS no_trailing_comma;Form Validation and Cleaning
Form input is a classic source of stray spaces, since users often add a space before or after typing an email or username. Trimming values before saving them prevents subtle bugs where '[email protected]' and '[email protected] ' are treated as two different accounts.
Example: Form Validation and Cleaning
CREATE TABLE users (id INT, email TEXT);
INSERT INTO users VALUES (1, '[email protected] '), (2, '[email protected]');
SELECT * FROM users WHERE TRIM(email) = '[email protected]';- Expecting
TRIMto remove spaces in the middle of a string, when it only removes them from the ends. - Trying to trim other characters without
TRIM(BOTH x FROM col). - Forgetting that
TRIMdoes not change the stored value unless you useUPDATE.
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: