Remove braces from regular expression result

Question

Problem

This code:

select
  x::text
from
  regexp_matches( 'i1 into o2, and g1 into o17', '[gio][0-9]{1,}', 'g' ) as x;

Returns these results:

{i1}
{o2}
{g1}
{o17}

Rather than the following results:

i1
o2
g1
o17

Related Links

• http://www.postgresql.org/docs/9.1/static/functions-matching.html

Question

What is the most efficient way to remove the braces using PostgreSQL 9.x?

Seriocomic

Answer 1

Optimal Solution

Your regexp_matches() pattern can only result in a single element per pattern evaluation, so all resulting rows are constrained to exactly one array element. The expression simplifies to:

SELECT x[1]
FROM   regexp_matches('i1 into o2, and g1 into o17', '[gio][0-9]{1,}', 'g') AS x;

Other Solutions

SELECT unnest(x)  -- also works for cases with multiple elements per result row

SELECT trim(x::text, '{}') -- corner cases with results containing `{}`

SELECT rtrim(ltrim(x::text, '{'), '}') AS x1 -- fewer corner cases

If the pattern can or shall not match more than one time per input value, also drop the optional parameter 'g'.

And if the function shall always return exactly one row, consider the subtly different variant regexp_match() introduced with Postgres 10.

In Postgres 10 or later it's also prudent to suggest the set-returning function (SRF) regexp_matches() in the SELECT list directly (like Rick provided) since behavior of multiple SRFs in the SELECT list has finally been sanitized:

• What is the expected behaviour for multiple set-returning functions in select clause?

Sublingual

Select ... regexp_matches(....)[1] from did not work for me. But unnest did. (The single pattern matches several times, generating multiple rows.) Thanks. – Tucky

@aberglas: The case of the OP (without capturing parentheses in the pattern) allows only one element in each resulting array. The fastest solution for that is the first one. If you can have more elements in the result, you need unnest(). I'll make that more clear. – Sublingual

If your query is formatted select regexp_matches(column_name, regular_expression) from table_name then unnest() is the easiest way to go: select unnest(regexp_matches(column_name, regular_expression)) from table_name – Headsman

@RickGladwin: Yes - with reservations. See updates to this old answer. – Sublingual