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

Postgresql – Unexpected error when trying to parse string

I have this table in PostgreSQL:

Table_T:

id body
1 bla bla bla Call ID: xxx Interaction ID: vvv bla bla
2 bla bla bla Call ID: zzz Interaction ID: ooo bla bla

This is the output I am expecting:

id Call_id
1 xxx
2 zzz

This the code I am using:

SELECT id
     , substring(body from position('Call ID:' in body)+8 for position('Interaction ID:' in body)- position('Call ID:' in body)+8)
FROM Table_T

I get this error:

Syntax error: Expected "(" or keyword UNNEST but got identifier at

Some documentation I reviewed:

What am I missing?

Tags:

2

Answers


  1. 0

    The error message seems unrelated. The expression works for me. Except that you seem to have your calculation wrong. And it can be done much simpler with substring() and a regular expression:

    SELECT id
     , substring(body FROM position('Call ID:' in body) + 9 FOR position('Interaction ID:' in body) - position('Call ID:' in body) - 10) AS proper
     , substring(body, 'Call ID: (.*) Interaction ID:') AS better
FROM   table_t;

    fiddle

    Login or Signup to reply.
  2. 0

    You can use regexp_match:

    select id, (regexp_match(body, '(?<=ID:s)w+'))[1] Call_id from Table_T

    See fiddle.

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

Back To Top
Search