How to use the split() method in SQL?

1 year ago
Benjamin Taylor
1 minute

There is no built-in SPLIT() function in SQL. However, you can use other methods to achieve the functionality of splitting strings.

One common method is to use the SUBSTRING_INDEX() function. This function can return a portion of a string before or after a specified delimiter. Here is an example:

SELECT 
    SUBSTRING_INDEX('apple,banana,orange', ',', 1) AS part1,
    SUBSTRING_INDEX(SUBSTRING_INDEX('apple,banana,orange', ',', 2), ',', -1) AS part2,
    SUBSTRING_INDEX('apple,banana,orange', ',', -1) AS part3;

In this example, we split the string ‘apple,banana,orange’ into three parts using a comma as the delimiter in the function SUBSTRING_INDEX(). The first parameter of the function is the string to be split, the second parameter is the delimiter, and the third parameter specifies which part to return.

The output is as follows:

part1    | part2    | part3
---------------------------
apple    | banana   | orange

If you need to extract multiple parts of a string, you can use the nested SUBSTRING_INDEX() function, as shown in the example above for part2.

I hope the above information is helpful to you! If you have any other questions, feel free to ask me anytime.

bannerAds