Question 114927 in PostgreSQL Question posted in
The official documentation can be found here.

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

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)
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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);

    Demo: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=5a3dd70a3793e3588d300bbb61dcf0b6

    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;

    Demo: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=72b728f15fe1d8b7a5c0cec8f32790fd

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search