How to extract a substring from a string with SQL

Problem

You have a string in your SQL database from which you want to extract a specific substring.

Sample data

Let's consider a database table users with a column email as:

In this scenario, we will extract the username part of the email (i.e., the part before '@').

MySQL Solution

SELECT 
    id,
    email,
    SUBSTRING_INDEX(email, '@', 1) AS username
FROM 
    users;

PostgreSQL Solution

SELECT 
    id,
    email,
    split_part(email, '@', 1) AS username
FROM 
    users;

Explanation

MySQL

In MySQL, the SUBSTRING_INDEX function is used to return a substring of the input string. It takes three arguments: the original string, the delimiter, and a number indicating the index of the substring. In our case, the delimiter is '@', and the index is '1' because we want the substring before '@'.

PostgreSQL

In PostgreSQL, the split_part function is used to split a string into multiple parts based on a delimiter and return the nth part. It takes three arguments: the original string, the delimiter, and an integer indicating which part to return. Here, we're splitting the email address on '@' and returning the first part, which is the username.