Extract text up to the Nth character in a string

Question

How can I extract the text up to the 4th instance of a character in a column?

I'm selecting text out of a column called filter_type up to the fourth > character.

To accomplish this, I've been trying to find the position of the fourth > character, but it's not working:

select substring(filter_type from 1 for position('>' in filter_type))

Answer 1

You can use the pattern matching function in Postgres.

First figure out a pattern to capture everything up to the fourth > character.

To start your pattern you should create a sub-group that captures non > characters, and one > character:

([^>]*>)

Then capture that four times to get to the fourth instance of >

([^>]*>){4}

Then, you will need to wrap that in a group so that the match brings back all four instances:

(([^>]*>){4})

and put a start of string symbol for good measure to make sure it only matches from the beginning of the String (not in the middle):

^(([^>]*>){4})

Here's a working regex101 example of that!

Once you have the pattern that will return what you want in the first group element (which you can tell at the online regex on the right side panel), you need to select it back in the SQL.

In Postgres, the substring function has an option to use a regex pattern to extract text out of the input using a 'from' statement in the substring.

To finish, put it all together!

select substring(filter_type from '^(([^>]*>){4})')
from filter_table

See a working sqlfiddle here

---

If you want to match the entire string whenever there are less than four instances of >, use this regular expression:

 ^(([^>]*>){4}|.*)

Answer 2

You can also use a simple, non-regex solution:

SELECT array_to_string((string_to_array(filter_type, '>'))[1:4], '>')

The above query:

1. splits your string into an array, using '>' as delimeter
2. selects only the first 4 elements
3. transforms the array back to a string