Postgres - Return default value if regex match fails
4
votes
3
answers
8193
views
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+')) as first_num
from
test_data
Asked by ryantuck
(173 rep)
Jun 19, 2018, 04:19 PM
Last activity: Apr 24, 2023, 02:04 AM
Last activity: Apr 24, 2023, 02:04 AM