How can I remove square bracket using regexp in postgresql-CodePudding

my_Query -

SELECT (regexp_matches('datavalue=Document{{value=[5]}}', 'datavalue=Document{{value=([^d}},''] )'))[1] response;

current output is square bracket - [5]

I require without square bracket - 5

CodePudding user response：

To extract a part of a string based on a regex, substring() is the better alternative:

substring('datavalue=Document{{value=[5]}}' from 'datavalue=Document{{value=\[([0-9] )\]}}') response;

CodePudding user response：

and what if multiple records in same row. Right now getting 5 (second value), In this case I require a NULL value (first value)

select substring('{id=1,datavalue=Document{{value=[]}}},{id=2,datavalue=Document{{value=[5]}}},'from 'datavalue=Document{{value=\[([0-9] )\]}}') response;