I have this table in PostgreSQL:

Table_T:

This is the output I am expecting:

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:

• https://www.postgresql.org/docs/current/functions-string.html
• https://www.w3resource.com/PostgreSQL/position-function.php
• getting "Syntax error: Expected "(" or keyword UNNEST but got identifier..." while converting oracle query to big query

What am I missing?

CodePudding user response：

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

CodePudding user response：

You can use regexp_match:

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

See fiddle.