Often in PostgreSQL you may want to remove the last character from a string field.
You can use the following syntax to do so:
SELECT *,
LEFT(team, -1) AS lastchar_removed
FROM athletes;
This particular example creates a new column named lastchar_removed that contains the string from the team column with the last character removed.
To remove a different number of characters from the end of the string, simply change the -1 in the LEFT function to a different number.
For example, you could use -2 to instead remove the last two characters from the string.
The following example shows how to use this syntax in practice.
Example: How to Remove Last Character from String in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball teams:
-- create table named 'athletes'
CREATE TABLE athletes (
athleteID INTEGER PRIMARY KEY,
team TEXT,
points INTEGER,
assists INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (0002, 'Hawks', 30, 12);
INSERT INTO athletes VALUES (0003, 'Kings', 12, 3);
INSERT INTO athletes VALUES (0004, 'Kings', 25, 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 31, 14);
INSERT INTO athletes VALUES (0006, 'Mavs', 16, 3);
INSERT INTO athletes VALUES (0007, 'Hawks', 10, 5);
INSERT INTO athletes VALUES (0008, 'Celtics', 14, 13);
INSERT INTO athletes VALUES (0009, 'Hawks', 20, 8);
INSERT INTO athletes VALUES (0010, 'Hawks', 26, 3);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | points | assists
-----------+---------+--------+---------
1 | Mavs | 22 | 5
2 | Hawks | 30 | 12
3 | Kings | 12 | 3
4 | Kings | 25 | 9
5 | Mavs | 31 | 14
6 | Mavs | 16 | 3
7 | Hawks | 10 | 5
8 | Celtics | 14 | 13
9 | Hawks | 20 | 8
10 | Hawks | 26 | 3
Suppose that we would like to remove the last character from each string in the team column.
We can use the following syntax to do so:
SELECT *,
LEFT(team, -1) AS lastchar_removed
FROM athletes;
athleteid | team | points | assists | lastchar_removed
-----------+---------+--------+---------+------------------
1 | Mavs | 22 | 5 | Mav
2 | Hawks | 30 | 12 | Hawk
3 | Kings | 12 | 3 | King
4 | Kings | 25 | 9 | King
5 | Mavs | 31 | 14 | Mav
6 | Mavs | 16 | 3 | Mav
7 | Hawks | 10 | 5 | Hawk
8 | Celtics | 14 | 13 | Celtic
9 | Hawks | 20 | 8 | Hawk
10 | Hawks | 26 | 3 | Hawk
Notice that the resulting table contains a new column named lastchar_removed that contains the string from the team column with the last character removed.
Feel free to use similar syntax to remove the last character from a string column in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: Extract Substring Before Specific Character
PostgreSQL: Extract Substring After Specific Character
PostgreSQL: How to Extract Substring Between Two Characters
