I'm trying to write a query in PostgreSQL to extract text between 4th and 5th , (comma)-CodePudding

This query works in Oracle please let me know how to extract text in postgresql

Select regexp_sustr('abc,afgg,afff,eef esfg eg,gegg,egegeg,vgb,grgg','[^,] ',1,5)

CodePudding user response：

You could use a regex replacement trick here:

SELECT REGEXP_REPLACE(
    'abc,afgg,afff,eef esfg eg,gegg,egegeg,vgb,grgg',
    '^([^,] ,){3}|,.*$', '', 'g') AS output;  -- eef esfg eg

Demo

This approach strips off the first 3 CSV terms and the 4th comma until the end of the string.

CodePudding user response：

Use split_part instead to efficiently obtain the n-th field given a delimiter:

select split_part('abc,afgg,afff,eef esfg eg,gegg,egegeg,vgb,grgg', ',', 5);

If you insist on using regex, the equivalent function to REGEXP_SUBSTR in PostgreSQL is regexp_matches:

select (regexp_matches('abc,afgg,afff,eef esfg eg,gegg,egegeg,vgb,grgg', '[^,] ','g'))[1] limit 1 offset 4;