To split a string in SQL you can use a built-in function. The exact function depends on which database you’re using.
Learn all about how to split a string in this guide.
Summary
Splitting a string involves converting one string value into multiple values, one in each row.
You can do this in each database:
- Oracle: REGEXP_SUBSTR and CONNECT BY
- SQL Server: STRING_SPLIT function
- MySQL: SUBSTRING_INDEX or JSON functions
- PostgreSQL: UNNEST and STRING_TO_ARRAY
Let’s look at some examples for each vendor.
You can download the SQL scripts used on this page on my GitHub repository here.
Oracle
You can split a string in Oracle SQL by using the REGEXP_SUBSTR and the CONNECT BY feature.
The REGEXP_SUBSTR function will split a string into multiple strings. The CONNECT BY feature will translate this into separate rows.
Let’s see some examples.
Example 1: Split a string by a space
This query will split a string into different rows when a space character is found.
SELECT
REGEXP_SUBSTR (
'The quick brown fox', '[^ ]+', 1, level
) AS string_parts
FROM dual
CONNECT BY REGEXP_SUBSTR (
'The quick brown fox', '[^ ]+', 1, level
) IS NOT NULL;
This query will first use “The quick brown fox” as the input string.
The second parameter uses the regular expression of ‘[^ ]+. This includes [^ ] which means any character except a space, and + which means one or more.
The third parameter is the starting position, which is 1.
The fourth parameter is a value that needs to be changed for each value to be searched. This is the level column that’s used by the CONNECT BY keyword.
We then use the same REGEXP_SUBSTR function after the CONNECT BY.
We add IS NOT NULL, which means that the CONNECT BY will stop processing when the string splitting process is completed.
Results:
string_parts |
The |
quick |
brown |
fox |
Example 2: Split a string by a comma
This query will split a string by comma values.
SELECT
REGEXP_SUBSTR (
'Once upon a time, there were three little ducks: Huey, Duey, and Louie.',
'[^,]+', 1, level) AS string_parts
FROM dual
CONNECT BY REGEXP_SUBSTR (
'Once upon a time, there were three little ducks: Huey, Duey, and Louie.',
'[^,]+', 1, level) IS NOT NULL;
Results:
VALUE |
Once upon a time |
there were three little ducks: Huey |
Duey |
and Louie. |
Example 3: Split a string value in a column
This query will split a value in a column that has comma-separated values.
Here’s our sample data, in a table called movie.
movie_title | category |
The Avengers | action,superhero,fantasy |
The Ring | horror,thriller |
Titanic | romance,drama |
Wedding Crashers | drama,comedy,romance |
This query will split the category value and display it in separate rows, along with the movie name.
SELECT
movie_title,
REGEXP_SUBSTR(
category,
'[^,]+', 1, level) AS split_category
FROM movie
CROSS JOIN LATERAL (
SELECT level rn
FROM dual
CONNECT BY level <=
length ( category ) - length ( replace ( category, ',' ) ) + 1
);
This uses the CROSS JOIN LATERAL feature to join the movie table data to the output of splitting category values using the CONNECT BY feature.
Here are the results:
movie_title | split_category |
The Avengers | action |
The Avengers | superhero |
The Avengers | fantasy |
The Ring | horror |
The Ring | thriller |
Titanic | romance |
Titanic | drama |
Wedding Crashers | drama |
Wedding Crashers | comedy |
Wedding Crashers | romance |
If you want to easily remember different SQL features, get a copy of my SQL Cheat Sheets here:
SQL Server
You can split a string in SQL Server by using the STRING_SPLIT function.
The function looks like this:
STRING_SPLIT (string, separator [, ordinal])
The parameters are:
- string (mandatory): the string that is to be split
- separator (mandatory): a single character that defines how you want to split the string
- ordinal (optional): if set to 1, it shows the position number of the string (e.g. 1, 2, 3).
It’s a table-valued function, which essentially means it returns a table of data and you can select a column from it.
This function was introduced in SQL Server 2016.
Let’s see some examples.
Example 1: Split a string by a space
This query will split a string into different rows when a space character is found.
SELECT value
FROM STRING_SPLIT('The quick brown fox', ' ');
Results:
VALUE |
The |
quick |
brown |
fox |
Example 2: Split a string by a comma
This query will split a string by comma values.
SELECT value
FROM STRING_SPLIT(
'Once upon a time, there were three little ducks: Huey, Duey, and Louie.', ','
);
Results:
VALUE |
Once upon a time |
there were three little ducks: Huey |
Duey |
and Louie. |
Example 3: Split a string using ordinal
This query uses the ordinal parameter to show the positions for each row.
SELECT value
FROM STRING_SPLIT('The quick brown fox', ' ', 1);
Results:
VALUE | ordinal |
The | 1 |
quick | 2 |
brown | 3 |
fox | 4 |
Example 4: Split a string value in a column
This query will split a value in a column that has comma-separated values.
Here’s our sample data, in a table called movie.
movie_title | category |
The Avengers | action,superhero,fantasy |
The Ring | horror,thriller |
Titanic | romance,drama |
Wedding Crashers | drama,comedy,romance |
This query will split the category value and display it in separate rows, along with the movie name.
SELECT
movie_title,
value
FROM movie
CROSS APPLY STRING_SPLIT(category, ',');
Here are the results:
movie_title | value |
The Avengers | action |
The Avengers | superhero |
The Avengers | fantasy |
The Ring | horror |
The Ring | thriller |
Titanic | romance |
Titanic | drama |
Wedding Crashers | drama |
Wedding Crashers | comedy |
Wedding Crashers | romance |
We can see the categories are split into separate rows.
MySQL
In MySQL, if you want to split a string into separate rows, you have two options:
- Use SUBSTRING_INDEX with a subquery that has many UNION ALL keywords
- Convert it to JSON and use JSON functions (available in MySQL 8.0 and later)
We’ll look at both methods in this guide.
Related: Ultimate Guide to JSON in MySQL
Example 1: Split a string by a space
This query will split a string into different rows when a space character is found.
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX(
'The quick brown fox', ' ', numbers.n
), ' ', -1
) AS string_parts
FROM
(SELECT 1 AS n UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5
) numbers
WHERE CHAR_LENGTH('The quick brown fox')
- CHAR_LENGTH(REPLACE('The quick brown fox', ' ', '')) >= numbers.n-1
ORDER BY n ASC;
This was inspired by a StackOverflow question. It’s not as clean as other database vendors, but here’s how it works:
- We use the substring_index function to find a substring up to a certain position. In this case, it’s the position of the space character.
- We use a series of UNION ALL statements to generate 5 rows, with the numbers of 1 to 5. This is used to calculate the first 5 substrings or split by the first 5 spaces. If you need more than this, you’ll have to add more numbers to this UNION ALL part.
- The WHERE clause is added to stop adding rows to the results when the last substring is found. It will stop at the last word of “fox”, rather than repeating the last row up until row number 5 is reached.
Here are the results:
string_parts |
The |
quick |
brown |
fox |
The sentence is split based on space characters.
Example 2: Split a string by a comma
This query will split a string by comma values.
Here’s the query.
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX(
'Once upon a time, there were three little ducks: Huey, Duey, and Louie.', ',', numbers.n
), ',', -1) AS string_parts
FROM
(SELECT 1 AS n UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5
) numbers
WHERE CHAR_LENGTH(
'Once upon a time, there were three little ducks: Huey, Duey, and Louie.'
) - CHAR_LENGTH(
REPLACE(
'Once upon a time, there were three little ducks: Huey, Duey, and Louie.', ',', ''
)
) >= numbers.n-1
ORDER BY n ASC;
We have the same query as example 1, except:
- We have a different string
- We are using commas instead of spaces as the delimiter.
Here are the results:
string_parts |
Once upon a time |
there were three little ducks: Huey |
Duey |
and Louie. |
Example 3: Split a string by a comma using JSON
This example uses the same input string as the previous example, except we will use MySQL JSON functions.
Here is our query:
SELECT
j.name
FROM JSON_TABLE(
REPLACE(
JSON_ARRAY(
'Once upon a time, there were three little ducks: Huey, Duey, and Louie.'
), ',', '","'
),
'$[*]' columns (name varchar(100) path '$')
) j;
We are using the JSON_TABLE function to transform the provided data into a table of records.
Inside the JSON_TABLE function, we replace our input string with commas in a quote. This can then be used in the rest of the JSON function, where we split them into separate rows.
Here are the results:
name |
Once upon a time |
there were three little ducks: Huey |
Duey |
and Louie. |
Example 4: Split a string value in a column
This query will split a value in a column that has comma-separated values.
Here’s our sample data, in a table called movie.
movie_title | category |
The Avengers | action,superhero,fantasy |
The Ring | horror,thriller |
Titanic | romance,drama |
Wedding Crashers | drama,comedy,romance |
This query will split the category value and display it in separate rows, along with the movie name.
Here’s the query:
SELECT
movie.movie_title,
SUBSTRING_INDEX(
SUBSTRING_INDEX(
movie.category, ',', numbers.n
), ',', -1
) AS category_name
FROM
(SELECT 1 AS n UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5
) numbers
INNER JOIN movie
ON CHAR_LENGTH(movie.category)
- CHAR_LENGTH(REPLACE(movie.category, ',', '')) >= numbers.n-1
ORDER BY movie_title, n;
This will show the movie title, and the category_name, which uses the same string split technique as the earlier examples.
We select from the movie table, where the data is stored.
We mention the category column both in the SELECT clause and in the JOIN clause as that is the field we are splitting.
Here are the results:
movie_title | split_category |
The Avengers | action |
The Avengers | superhero |
The Avengers | fantasy |
The Ring | horror |
The Ring | thriller |
Titanic | romance |
Titanic | drama |
Wedding Crashers | drama |
Wedding Crashers | comedy |
Wedding Crashers | romance |
The categories are split into separate rows.
If you want to easily remember different SQL features, get a copy of my SQL Cheat Sheets here:
PostgreSQL
If you want to split a string into separate rows in PostgreSQL, you can use the UNNEST function and the STRING_TO_ARRAY function.
The UNNEST function expands an array to a number of rows. The STRING_TO_ARRAY function converts a string value to an array.
Let’s see an example of this.
Example 1: Split a string by a space
This query will split a string into different rows when a space character is found.
SELECT
UNNEST(
STRING_TO_ARRAY('The quick brown fox', ' ')
) AS string_parts;
This will first convert the string of “The quick brown fox” to an array, splitting by space characters.
Then, it will convert that array to separate rows, using the UNNEST function.
Here are the results.
string_parts |
The |
quick |
brown |
fox |
We can see there is a separate row for each word.
Example 2: Split a string by a comma
This query will split a string by comma values.
Here’s the query:
SELECT UNNEST(
STRING_TO_ARRAY(
‘Once upon a time, there were three little ducks: Huey, Duey, and Louie.’, ‘,’
)
) AS string_parts;
It’s the same as the earlier example, but we use a comma as the separator instead of a space.
Here are the results:
STRING_PARTS |
Once upon a time |
there were three little ducks: Huey |
Duey |
and Louie. |
Example 3: Split a string value in a column
This query will split a value in a column that has comma-separated values.
Here’s our sample data, in a table called movie.
movie_title | category |
The Avengers | action,superhero,fantasy |
The Ring | horror,thriller |
Titanic | romance,drama |
Wedding Crashers | drama,comedy,romance |
This query will split the category value and display it in separate rows, along with the movie name.
Here’s the query:
SELECT
movie_title,
UNNEST(
STRING_TO_ARRAY(movie.category, ',')
) AS category_name
FROM movie;
Just like the earlier examples, we use both UNNEST and STRING_TO_ARRAY. We are splitting the movie.category column, based on a comma. We also want to see the movie_title.
Here are the results:
movie_title | category_name |
The Avengers | action |
The Avengers | superhero |
The Avengers | fantasy |
The Ring | horror |
The Ring | thriller |
Titanic | romance |
Titanic | drama |
Wedding Crashers | drama |
Wedding Crashers | comedy |
Wedding Crashers | romance |
We can see there is one row for each category, and the movie_title is repeated.
Conclusion
Splitting a string into separate rows is possible in an SQL database. The method is different for each vendor, but it’s possible and can be useful if you have data you want to transform in this way.
There’s a lot to remember with SQL, but with my SQL Cheat Sheets, the main features are all in a simple PDF. Get a copy of my SQL Cheat Sheets here:
Hey sry i posted the question under the wrong articel first.
But when i try youre orcale example.
SELECT
movie_title,
REGEXP_SUBSTR(
category,
‘[^,]+’, 1, level) AS split_category
FROM movie
CROSS JOIN LATERAL (
SELECT level rn
FROM dual
CONNECT BY level <=
length ( category ) – length ( replace ( category, ',' ) ) + 1
);
i get the following error:
ORA-01788: Klausel CONNECT BY in diesem Abfrageblock erforderlich
01788. 00000 – "CONNECT BY clause required in this query block"
Do you know how to fix it?
I tried Oracle sql
SELECT
movie_title,
REGEXP_SUBSTR(
category,
‘[^,]+’, 1, level) AS split_category
FROM movie
CROSS JOIN LATERAL (
SELECT level rn
FROM dual
CONNECT BY level <=
length ( category ) – length ( replace ( category, ',' ) ) + 1
);
and got error:
ORA-01788: CONNECT BY clause required in this query block
01788. 00000 – "CONNECT BY clause required in this query block"
*Cause:
*Action:
Error at Line: 6 Column: 6
I found the error in the sql. Try this one:
SELECT
movie_title,
REGEXP_SUBSTR(
groups,
‘[^,]+’, 1, rn) split_category
FROM movie
CROSS JOIN LATERAL (
SELECT level rn
FROM dual
CONNECT BY level <=
length ( category ) – length ( replace ( category , ',' ) ) + 1
);