Often in PostgreSQL you may want to remove both the first and the last character from a string field.
You can use the following syntax to do so:
SELECT *,
substr(team, 2, length(team) - 2) AS new_team
FROM athletes;
This particular example creates a new column named new_team that removes the first and last character from each string in the team column of the athletes table.
Note that we use the substr() function to extract the substring in the team column ranging from index position 2 to the length of the string minus 2.
This has the effect of removing the first and last character from the string.
The following example shows how to use this syntax in practice.
Example: How to Remove First and Last Character from String in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
athleteID INTEGER,
team TEXT,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Celtics', 22);
INSERT INTO athletes VALUES (0002, 'Hawks', 30);
INSERT INTO athletes VALUES (0003, 'Kings', 12);
INSERT INTO athletes VALUES (0004, 'Kings', 25);
INSERT INTO athletes VALUES (0005, 'Grizzlies', 31);
INSERT INTO athletes VALUES (0006, 'Mavs', 16);
INSERT INTO athletes VALUES (0007, 'Hawks', 10);
INSERT INTO athletes VALUES (0008, 'Celtics', 14);
INSERT INTO athletes VALUES (0009, 'Thunder', 20);
INSERT INTO athletes VALUES (0010, 'Hawks', 26);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | points
-----------+-----------+--------
1 | Celtics | 22
2 | Hawks | 30
3 | Kings | 12
4 | Kings | 25
5 | Grizzlies | 31
6 | Mavs | 16
7 | Hawks | 10
8 | Celtics | 14
9 | Thunder | 20
10 | Hawks | 26
Suppose that we would like to remove the first and last character from each string in the team column.
We can use the following syntax to do so:
SELECT *,
substr(team, 2, length(team) - 2) AS new_team
FROM athletes;
athleteid | team | points | substr
-----------+-----------+--------+---------
1 | Celtics | 22 | eltic
2 | Hawks | 30 | awk
3 | Kings | 12 | ing
4 | Kings | 25 | ing
5 | Grizzlies | 31 | rizzlie
6 | Mavs | 16 | av
7 | Hawks | 10 | awk
8 | Celtics | 14 | eltic
9 | Thunder | 20 | hunde
10 | Hawks | 26 | awk
Notice that the resulting table contains a new column named new_team that contains the string from the team column with the first and last characters removed.
You can use similar syntax to remove the first and 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
