Postgres - Return default value if regex match fails

Question

I'd like to attempt a regex match, and return null if it fails.

The following query attempts to find the first number in a string. The result ignores the entry with text 'blah'. I'd prefer it to return a null value instead.

This question is probably less about regex and more about set algebra. My hunch is that there is an elegant way to do it, without needing to left join anything, though googling is proving futile.

with test_data as (
  select 'abc 123' as txt
  union
  select 'abc 456' as txt
  union
  select 'blah' as txt
)

select
  txt,
  (regexp_matches(txt, '\d+'))[1] as first_num
from
  test_data

Erwin Brandstetter · Accepted Answer · 2022-04-05 19:08:16Z

While your answer solves the problem with regexp_matches(), the much better solution is to avoid the problem with the more appropriate function substring() to begin with:

WITH test_data(txt) AS (
   VALUES
      (text 'abc 123')
    , ('abc 456')
    , ('blah')
   )
SELECT txt, substring(txt FROM '\d+') AS first_num
FROM   test_data;

Does exactly what you need: returns the first match in the string or NULL for no match.

substring(string FROM pattern) is standard SQL syntax.
Don't confuse it with substring(string [FROM int] [FOR int]) or substring(string FROM pattern FOR escape) doing something else.

You can also use the shorter, equivalent, currently undocumented Postgres implementation substring(text, text) (with comma (,) instead of FROM).

Use regexp_matches() to return all matches as set. (Note that a single regexp can result in multiple matches, hence a set of text arrays). You still only get the first row unless you add the flag 'g' to get all rows (additional matches - if any - for the same regexp). Related:

Reverse Byte-Order of a postgres bytea field

To avoid complications move set-returning functions to the FROM list in versions before Postgres 10:

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

Or use regexp_match() in Postgres 10 or later to only return the first row. This would also not exhibit the original problem:

WITH test_data(txt) AS (
   VALUES
      (text 'abc 123')
    , ('abc 456')
    , ('blah')
   )
SELECT txt, (regexp_match(txt, '\d+'))[1] AS first_num
from   test_data;

But just use substring() for your purpose.

db<>fiddle here

ryantuck · Accepted Answer · 2018-06-19 16:25:39Z

1

Use a sub-select.

From the docs:

It is possible to force regexp_matches() to always return one row by using a sub-select; this is particularly useful in a SELECT target list when you want all rows returned, even non-matching ones

The solution is:

with test_data as (
  select 'abc 123' as txt
  union
  select 'abc 456' as txt
  union
  select 'blah' as txt
)

select
  txt,
  (select regexp_matches(txt, '\d+'))[1] as first_num
from
  test_data

answered Jun 19, 2018 at 16:25

ryantuck

1731 silver badge7 bronze badges

Add a comment |

jian · Accepted Answer · 2023-04-24 02:04:07Z

0

minor optimization. also match optional prefix numeric value characters +-.

WITH test_data (
    txt
) AS (
    VALUES (text 'abc 123'),
        ('abc 456'),
        ('blah'),
        ('11blah'),
        ('-.1-blah'),
        ('.1-blah'),
        ('-1-blah'),
        ('+.123'),
        ('abc+456'),
        ('abc-456'),
        ('abc-.456'),
        ('1.1-'),
        ('+11'))
SELECT
    txt,
    substring(txt FROM '[-+]?[.]?\d+')::numeric AS regex_num
FROM
    test_data;

answered Apr 24, 2023 at 2:04

jian

4874 silver badges18 bronze badges

Add a comment |

Stack Exchange Network

Postgres - Return default value if regex match fails

3 Answers 3

Linked

Hot Network Questions