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

postgresql extract substring from some position

I have a table named marc and column named tag
this column contain a text like this:

"|a john dumas .|d 1941|e united states|=^A22306"

I want to read this text and extract only a substring which is between |a and |d.

the text should be this: john dumas.1941

I tried without success with:

select regexp_replace(tag, '(.*)_[^|][^a][^d]*$', '1')
from marc where id='10825700016';

can someone help me to solve this problem

Tags:

2

Answers


  1. 0

    You can use substring() to extract the part between |a and |e.

    Then use replace() to get rid of the .|d

    replace(substring(tag from '|a(.+)|e'), '.|d', '')
    Login or Signup to reply.
  2. 0

    I want to read this text and extract only a substring which is between
    |a and |d.

    You can do it using String functions such as CONCAT and substring like in this DBFIDDLE

    SELECT 
    CONCAT(
        trim(substring(tag, position('|a' in tag) + 2, position('|d' in tag) - position('|a' in tag) - 3)),
        '.',
        trim(substring(tag, position('|d' in tag) + 2, position('|e' in tag) - position('|d' in tag) - 2))
    ) AS extracted_string
FROM marc
WHERE id = '10825700016';

    The trim function will also remove the leading spaces before john and after dumas

    Output:

    extracted_string
john dumas.1941
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search