FB

How to Split a String in SQL: Guide & Examples

Leave a Comment / Last updated: October 3, 2022

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

 

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.

 

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.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Table of Contents

→ Table of Contents