What regex to use to take only first occurrence of number in string?

Question

I have table with comments like this

'Payment amount: 11000,50 from 144232'

'Payment amount: 13 450,20 from 144232'

Sometimes white spaces occurs in number, because people typing this manually. I need to get first numbers like 11000,50 and 13 450,20 from example.

I'm trying to use regexp_replace('Payment amount: 11000,20 from 144232','([a-zA-Z:\s])','','g') and get result '11000,20144232', but I need only '11000,00'

How can I improve regex or what function I need to use to get this numbers?

you want to extract only that value?

Alberto
– Alberto

2020-06-29 12:07:52 +00:00
Commented Jun 29, 2020 at 12:07 — Alberto
– Alberto, Commented Jun 29, 2020 at 12:07
I need to get first number with two numbers after comma

FeoJun
– FeoJun

2020-06-29 12:25:00 +00:00
Commented Jun 29, 2020 at 12:25 — FeoJun
– FeoJun, Commented Jun 29, 2020 at 12:25

The fourth bird · Accepted Answer · 2020-06-29 14:31:37Z

1

To get the first number in the string with a comma and 2 digits after it, and spaces can occur between the digits due to typing:

^[a-zA-Z:\s]*(\d[\s\d]*,\s*\d\s*\d)

Explanation

^ Start of string
[a-zA-Z:\s]* Match 0+ times any of the listed chars in the character class
( Capture group 1 (this will contain the value)
- \d Match a digit
- \d[\s\d]* Match 0+ times a whitespace char or digit
- ,\s*\d\s*\d Match a comma and 2 digits with optional whitespace chars (Add \M if there can be no more word character following)
) Close group 1

Regex demo | Postgresql demo

A broader match could be to match 0+ times any char except a digit \D* instead of using [a-zA-Z:\s]*

Regex demo

edited Jun 29, 2020 at 14:31

answered Jun 29, 2020 at 14:17

The fourth bird

165k16 gold badges61 silver badges75 bronze badges

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

Comments

mottosson · Accepted Answer · 2020-06-29 12:28:07Z

0

This works for your examples, with or without decimals:

^[a-zA-Z:\s]+([\d\s]*(,\d{1,2})?)

https://regex101.com/r/1JEe3F/1

edited Jun 29, 2020 at 12:28

answered Jun 29, 2020 at 12:09

mottosson

3,8535 gold badges40 silver badges85 bronze badges

Comments

Laurenz Albe · Accepted Answer · 2020-06-29 12:53:58Z

0

I would do it like this:

SELECT (regexp_match(
           'Payment amount: 11000,00 from 144232',
           '[[:digit:]][[:digit:] ]*(?:,[[:digit:]]+)')
       )[1];

 regexp_match 
--------------
 11000,00
(1 row)

edited Jun 29, 2020 at 12:53

answered Jun 29, 2020 at 12:24

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

2 Comments

FeoJun Over a year ago

Sometimes happens that 1st value like '11 000,00' with whitespace between hundredths. How can I solve this case?

Laurenz Albe Over a year ago

I changed the expression to cover that. But don't expect any expression like that to magically always do the right thing with arbitrary data.

Stefanov.sm · Accepted Answer · 2020-06-29 13:29:51Z

0

Somewhat simpler but less strict, ([+\-]?\d[\d ,\.]+).*

SELECT (regexp_matches('Payment amount: 11,000.00 from 144232', '([+\-]?\d[\d ,\.]+).*'))[1];

Result: 11,000.00

edited Jun 29, 2020 at 13:29

answered Jun 29, 2020 at 13:22

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Collectives™ on Stack Overflow

What regex to use to take only first occurrence of number in string?

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related