Postgresql substring after character

by

Query: PostgreSQL substring after character

In PostgreSQL, you can substring a string after a specific character using the
SUBSTRING function along with other string functions like POSITION.
Let’s consider an example to understand this better.

Example:


-- Let's say we have a table named 'employees' with a column 'full_name'
-- containing employee names in the format "first_name last_name"

CREATE TABLE employees(
    id SERIAL,
    full_name VARCHAR(100)
);

INSERT INTO employees (full_name)
VALUES ('John Doe'),
       ('Jane Smith'),
       ('Michael Johnson');
       
-- Now, we want to get the last names of the employees from the 'full_name' column.
-- We can achieve this using the SUBSTRING and POSITION functions.

SELECT SUBSTRING(full_name, POSITION(' ' IN full_name) + 1) AS last_name
FROM employees;

The above query will return the last names of the employees by finding the space character
using the POSITION function, and then using SUBSTRING to extract the
substring after the space character (+1 to exclude the space).

The result of the above query would be:


+------------+
| last_name  |
+------------+
| Doe        |
| Smith      |
| Johnson    |
+------------+

So, in this example, we are subtracting the first name and space from the original
full_name column value to extract the last name using the
SUBSTRING and POSITION functions in PostgreSQL.

Leave a comment