Extract Number from String Field, Including Decimal (Postgres 9.5)

Question

Suppose I have a field:

product_strength
10MG/ML
0.25MG
25MG
0.125MG

How do I extract just the "numeric" part and then cast to numeric? I can get this far: regexp_replace(product_strength, '(\D|!\.)','','g')::numeric AS result_numeric

But the problem with this is that it doesn't actually account for the decimal. In other words, this returns

product_strength result_numeric
10MG/ML             10
0.25MG              25
25MG                25
0.125MG             125

But I would want to return

product_strength result_numeric
10MG/ML             10
0.25MG              0.25
25MG                25
0.125MG             0.125

score 6 · Accepted Answer · 2017-07-10 20:38:46Z

6

I would use regexp_matches for this:

select (regexp_matches(product_strength, '[0-9]+\.?[0-9]*'))[1]::numeric
from the_table

regexp_matches() returns an array of all matched strings, that's why the [1] is needed.

edited Jul 10, 2017 at 20:38

answered Jul 10, 2017 at 20:36

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Tom Wyllie Over a year ago

This pattern matches the empty string ''

user3871 Over a year ago

@a_horse_with_no_name this doesn't match all instances of number. For example, 12a15b => {12}. What I need is {12,15}. I've tried the g modifier: (regexp_matches(product_strength, '[0-9]+\.?[0-9]*', 'g')) but this didn't work

Tom Wyllie · Accepted Answer · 2019-07-01 14:52:04Z

2

Try this regex to match the numbers;

\d+\.?\d*

Edit: as "Boolean_Type" says, if you need negative numbers too, you could add in an optional negative sign, and use;

\-?\d+\.?\d*

edited Jul 1, 2019 at 14:52

answered Jul 10, 2017 at 20:32

Tom Wyllie

2,08215 silver badges16 bronze badges

3 Comments

Vincent Over a year ago

My actual use case is in sqlalchemy, and I have: sa.cast(sa.func.regexp_matches(col, '\d+\.?\d*'), sa.Numeric). How can I access the first element of the array returned by sa.func.regexp_matches? I tried sa.cast(sa.func.regexp_matches(col, '\d+\.?\d*')[0], sa.Numeric) but that didn't work

Vincent Over a year ago

Ah nevermind, I was actually able to use the substring function. So this worked for me: substring (product_strength, '\d+\.?\d*')::numeric or in sqlalchemy: sa.cast(sa.func.substring(col, '\d+\.?\d*'), sa.Numeric)

Boolean_Type Over a year ago

This solution takes into account possible negative numbers: \-?\d+\.?\d*.

Collectives™ on Stack Overflow

Extract Number from String Field, Including Decimal (Postgres 9.5)

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related