How to split a string in SQL

Problem

You have a string in one of your SQL database fields and you want to split it into separate parts based on a specific delimiter or character.

Sample Data

Let's consider a table Users with a field named fullname that contains the full names of users in the format "firstname_lastname".

MySQL Solution

SELECT 
    UserId, 
    SUBSTRING_INDEX(fullname, '_', 1) as firstname, 
    SUBSTRING_INDEX(fullname, '_', -1) as lastname 
FROM 
    Users;

MySQL Explanation

In MySQL, the function SUBSTRING_INDEX() is used to split strings. This function takes three parameters: the original string, the delimiter, and a number indicating the index of the part you want to retrieve. If the number is positive, it retrieves the part before the delimiter. If the number is negative, it retrieves the part after the delimiter.

In this solution, we use SUBSTRING_INDEX(fullname, '_', 1) to get the first name and SUBSTRING_INDEX(fullname, '_', -1) to get the last name.

PostgreSQL Solution

SELECT 
    UserId, 
    SPLIT_PART(fullname, '_', 1) as firstname, 
    SPLIT_PART(fullname, '_', 2) as lastname 
FROM 
    Users;

PostgreSQL Explanation

PostgreSQL uses the function SPLIT_PART() to split strings. This function also takes three parameters: the original string, the delimiter, and an integer indicating the part you want to retrieve. The function splits the string at the delimiter and returns the nth substring.

In this solution, we use SPLIT_PART(fullname, '_', 1) to get the first name and SPLIT_PART(fullname, '_', 2) to get the last name. Note that unlike MySQL, PostgreSQL does not use negative indexes. Instead, you would increment the index to get to the next part of the split string.