Is there a way to simplify this query using only one regexp_replace?
select regexp_replace(regexp_replace('BL 081', '([^0-9.])', '', 'g'), '(^0+)', '', 'g')
the result should be 81
I'm trying to remove all non-numeric chars and leading 0's from the result
You can do this by capturing the digits you want (not including any leading zeros) and removing everything else:
select regexp_replace('BL 0081', '.*?([1-9][0-9]*)$', '\1')
Output
81
Note you don't need the g flag as you are only making one replacement.
Why not just change the range from 0-9 to 1-9?
regexp_replace('BL 081', '(^[^1-9]+)', '', 'g')
This pattern should do: \D+|(?<=\s)0+
\D - matches characters that are not digits
(?<=\s) - looks behind for spaces and matches leading zeros
You can use 1 fewer regexp_replace:
select regexp_replace('BL 081', '\D+|(?<=\s)0+', '', 'g')
# outputs 81
alternatively, if you are interested in the numeric value, you could use a simpler regex and then cast to an integer.
select regexp_replace('BL 081', '\D+', '')::int
# also outputs 81, but its type is int
