SQL Split String by Delimiter

This article will discuss the various ways you can split a string by a delimiter in several SQL database engines.

Standard SQL Split String

The first method is on how to use Standard SQL to split a string by a delimiter. For that, we can use the split() function as shown in the syntax below:

SPLIT(value[, delimiter])

The function takes the string and the delimiter as the arguments. Then, it splits the string based on the specified delimiter.

The function will split a string based on a comma delimiter by default. However, you must specify the target delimiter for bytes.

If you specify an empty delimiter, the function will return an array of UTF-8 characters from the string values. Likewise, if the input is a byte, the function will return an array of bytes.

Consider the following example code:

SELECT
SPLIT('a b c d e f g', ' ') AS arr;

In the previous query, we use the split method to split the string using a space as the delimiter. The function should return an array of strings as shown below:

arr
"[a,b,c,d,e,f,g]"

(NOTE: The previous example is an array of strings.)

You can also specify a custom delimiter as illustrated in the example code below:

SELECT
SPLIT('a,b,c,d,e,f,g', ',') AS arr;

In this example, we specify the delimiter as a comma, which should return a value as shown below:

arr
"[a,b,c,d,e,f,g]"

If you provide an empty string, the function will return an empty array. This is illustrated in the example below:

SELECT
SPLIT('', '') AS arr;

The previous query should return an output as shown below:

arr
[]

MySQL Split String

In MySQL, we can use the substring_index() function to split a string based on the delimiter. The function syntax is provided below:

SUBSTRING_INDEX(string, delimiter, count)

The function takes the string, delimiter, and the count as the arguments. Then, it will return the number of strings based on the count split by the provided delimiter.

An example usage is shown below:

SELECT SUBSTRING_INDEX('a,b,c,d,e,f', ',', 6);

The previous query should return as follows:

a,b,c,d,e,f

To explore more about the MySQL Split string, check the resource provided below:

https://linuxhint.com/split-string-in-mysql/

PostgreSQL Split String

In PostgreSQL, you can split a string using the split_part() function. This function performs closely similar to the substring_index() in MySQL.

The function syntax is as shown:

SPLIT_PART(string, delimiter, position)

The function takes the string to split, the delimiter to use, and the part of the string to return. The position parameter must be a positive integer starting at 1.

We can look at an example as shown below:

select split_part(‘a,b,c,d,e,f,g’, ‘,’, 1);

We split the string based on a comma in the previous example and get the first string. The output returns as shown below:

split_part
a

SQL Server Split String

In SQL Server, it can use the string_split() function to split a string based on the delimiter. The syntax is shown below:

STRING_SPLIT (string , delimiter )

An example usage is provided below:

SELECT
value
FROM
STRING_SPLIT('a,b,c,d', ',');

This should return as follows:

value
--------------------
a
b
c
d

Conclusion

This article discussed various methods and functions to split a string by delimiter across multiple database engines. We hope you found this article helpful. Check the other Linux Hint articles for more tips and tutorials.