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?

CodePudding user response：

You can use regexp_matches:

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

CodePudding user response：

For a string result, this:

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