how to get characters between [ ] in postgresql

Question

I want get characters between [] from a string

example:

"hello" and "world"

from

"gegerferf[hello] frfer [world] frfre rfrf"

into a table

i am try with substring and for loop but i dont now.

select regexp_replace(string, '...','', 'g')
from mytable

Answer 1

The question didn't provide an example of the expected results, so I'll present a couple of options.

The following is run to setup the demonstration context:

CREATE TABLE t (
  id serial primary key,
  string text
);

INSERT INTO t(string)
VALUES ('gegerferf[hello] frfer [world] frfre rfrf');

This query returns the text between brackets concatenated into a single string:

SELECT regexp_replace(string, '[^[]*\[([^]]*)\][^[]*', '\1', 'g')
  FROM t;

The result is:

 regexp_replace 
----------------
 helloworld
(1 row)

This query returns the text between brackets as separate rows:

SELECT (regexp_matches(string, '\[([^]]*)\]', 'g'))[1]
  FROM t;

The output is:

 regexp_matches 
----------------
 hello
 world
(2 rows)

Answer 2

SELECT  (REGEXP_MATCHES('gegerferf[hello] frfer [world] frfre rfrf','(?:\[)(.*?)(?:\])','g'))[1];

• .* (dot asterisk) by default is greedy, you need non-greedy, so use left Parentheses dot asterisk right Parentheses (.*?)

• you need escape brackets [], see post here: Escape function for regular expression or LIKE patterns

• you don't want capturing brackets [], so you need non-capturing. see: https://www.postgresql.org/docs/current/functions-matching.html so you need (?:re)

• SETOF text[] is output data types for regexp_matches, so you need do array subscript in the end.