PostgreSQL - Substring to nth occurence of character

user_14034750 0
Viewed 58

I'm trying to run a query that returns the substring of a value up to the nth occurence of a particular character.

For example:

This is the value abc|123|xyz098|password|xxxxx and I'd like to get abc|123|xyz098 So everything up to the third '|'. The number of characters between the delimiters changes, so I can't use a fixed index.

My thinking is to use SUBSTRING() with POSITION(). But I'm not having any success using it it to get to the nth occurence. Does anyone have any solutions?

postgresql sql
2 Answers

You can use regexp_matches:

select regexp_matches('abc|123|xyz098|password|xxxxx', '\w+\|\w+\|\w+(?=\|)')
user_73267380

For a string result, this:

select split_part('abc|123|xyz098|password|xxxxx', split_part('abc|123|xyz098|password|xxxxx','|',4),1);
user_150623610
Related