PostgreSQL: Remove Last Character from String

You’ve got messy data with trailing characters you don’t need. Maybe CSV imports have trailing commas, phone numbers have extra digits, or text fields end with unwanted punctuation. Whatever the reason, you need to chop off that last character.

Quick Answer: Use LEFT Function

To remove the last character from a string, use the LEFT function:

SELECT
    emp_id,
    first_name,
    phone,
    LEFT(phone, LENGTH(phone) - 1) as phone_cleaned
FROM employees
WHERE phone IS NOT NULL
LIMIT 5;

Returns:

emp_id | first_name | phone         | phone_cleaned
-------+------------+---------------+------------------
     1 | James      | 555-1234-     | 555-1234
     2 | Mary       | 555-5678-     | 555-5678

That’s it. LEFT(string, count) takes the first N characters, and LENGTH(string) - 1 means “all but the last character.”

How It Works

The LEFT function extracts a substring starting from the beginning. By using LENGTH(string) - 1, you’re saying “give me all characters except the last one.”

It’s simple and direct. PostgreSQL calculates the length once, subtracts 1, and extracts everything up to that position.

Different Approaches & When to Use Them

Approach 1: LEFT Function (Most Readable)

The LEFT function is the clearest way to express your intent:

SELECT
    text_field,
    LEFT(text_field, LENGTH(text_field) - 1) as trimmed
FROM data_table
WHERE LENGTH(text_field) > 1;

Best for: When readability matters and you’re doing this operation a lot.

Approach 2: SUBSTRING Function (Most Flexible)

SUBSTRING gives you more control if you need it later:

SELECT
    text_field,
    SUBSTRING(text_field, 1, LENGTH(text_field) - 1) as trimmed
FROM data_table;

Explicitly stating “from position 1” makes it clear what you’re doing, though it’s slightly more verbose than LEFT.

Best for: When you might need to extend the logic later (like removing multiple characters or starting from different positions).

Approach 3: Conditional Removal (Safe)

Check the length first to avoid empty results:

SELECT
    text_field,
    CASE
        WHEN LENGTH(text_field) > 1
        THEN LEFT(text_field, LENGTH(text_field) - 1)
        ELSE NULL  -- or return empty string ''
    END as safely_trimmed
FROM data_table;

This prevents issues if you have single-character strings that would become empty.

Best for: Production queries where data quality varies.

Approach 4: Conditional Removal by Character

Only remove the last character if it matches a pattern:

SELECT
    text_field,
    CASE
        WHEN RIGHT(text_field, 1) = ','
        THEN LEFT(text_field, LENGTH(text_field) - 1)
        ELSE text_field
    END as remove_if_comma
FROM csv_imports;

This is useful when you only want to remove specific trailing characters.

Best for: Cleaning data where not all rows need trimming.

Real-World Example: Clean CSV Imports

You’re importing CSV data and the last column has trailing commas. Here’s how to clean it:

SELECT
    id,
    name,
    email,
    CASE
        WHEN RIGHT(status, 1) = ','
        THEN LEFT(status, LENGTH(status) - 1)
        ELSE status
    END as status_cleaned
FROM csv_imports
WHERE import_date = CURRENT_DATE
ORDER BY id;

Then use this cleaned data to insert into your main table. Much better than having commas in your production data.

Update Columns Permanently

If you’re fixing data issues, update the column in-place:

UPDATE employees
SET phone = LEFT(phone, LENGTH(phone) - 1)
WHERE LENGTH(phone) > 1
  AND RIGHT(phone, 1) IN ('-', ' ');  -- Only if ends with dash or space

-- Verify the change
SELECT emp_id, phone FROM employees LIMIT 5;

The WHERE clause ensures you don’t create empty strings and only clean rows that need it.

Performance Comparison

Method Speed Readability
LEFT Very Fast Excellent
SUBSTRING Very Fast Good
CASE + condition Fast Good

All are extremely fast. Choose based on readability and whether you need conditionals.

Decision Guide

Scenario Use This Why
Simple removal LEFT Clearest, simplest
Need flexibility SUBSTRING More control
Avoid empty results CASE Safety
Remove specific char RIGHT + CASE Target specific char
Update table LEFT + UPDATE + WHERE With safety conditions

Edge Cases & Handling

Single character strings:

-- Returns empty string
SELECT LEFT('A', LENGTH('A') - 1);  -- Result: ''

-- Use NULLIF to return NULL instead of empty
SELECT NULLIF(LEFT(text, LENGTH(text) - 1), '');

NULL values:

-- LEFT on NULL returns NULL
SELECT LEFT(NULL, 5);  -- Result: NULL

-- Use COALESCE to provide default
SELECT COALESCE(LEFT(text, LENGTH(text) - 1), 'N/A');

Very long strings: PostgreSQL handles this efficiently. Even for 100MB text fields, the operation is fast.

FAQ

Q: What if the string has only 1 character? A: It becomes empty string. Use CASE to check: WHEN LENGTH(text) > 1 THEN LEFT(...).

Q: Does this work with special characters? A: Yes. All characters are treated the same. LEFT('café', 4) becomes ‘caf’.

Q: Can I remove more than one character from the end? A: Use a larger number: LEFT(text, LENGTH(text) - 3) removes last 3 characters.

Q: What about removing specific trailing characters? A: Use TRIM: TRIM(TRAILING ',' FROM text) removes trailing commas specifically.

Q: Is there a performance difference between LEFT and SUBSTRING? A: No, they’re essentially identical at the machine level. PostgreSQL optimizes both the same way.

Q: Can I remove only if the last character is whitespace? A: Yes, use TRIM: TRIM(TRAILING FROM text) or with CASE: WHEN RIGHT(text, 1) = ' ' THEN LEFT(...).

Q: What’s the difference between LEFT and SUBSTRING? A: LEFT is shorter syntax. SUBSTRING is more explicit. Functionally identical for this use case.

Wrapping Up

Removing the last character from a string in PostgreSQL is straightforward. Use LEFT(text, LENGTH(text) - 1) for simplicity, add a CASE condition if you need safety, and update your table when you’re cleaning data.

The key: LENGTH(string) - 1 gives you all but the last position. That’s the core of this operation.