Postgres Parse String with Delimiter

Question

Trying to parse a sting using SQL, and have not found any solutions online (apologies, maybe I'm looking for the wrong thing).

I have a string field with a series of numbers I need to pull out and sum. Delimiter is "\r\n".

Example: '\r\n - 1234 somenumbersandtext123 \r\n -5678 sometextmorenumbers123'

So in this example, I want to sum 1234 and 5678.

The stings are all different lengths, and I need to eventually sum the numbers within the string. The string details documents tied to a project, and the numbers represent the size of the file (trying to determine the total file size per project).

Thanks in advance for any guidance.

Here's what I have so far: sqlfiddle.com/#!17/73df7/1 using regexp_split_to_table — boognish
– boognish, Commented Aug 6, 2018 at 5:18
If you need to sum all numbers in the particular string: with t(x) as (values('- 232554 /sometext.pdf\r\n- 27491.70 /morewords\r\n- 138283 /filename.pdf\r\n')) select n[1]::numeric from t, regexp_matches(x,'\d+\.?\d*','g') as n; — Abelisto
– Abelisto, Commented Aug 6, 2018 at 6:09
@Abelisto thanks for the response. I should have noted, my text strings can contain numbers, so I'm not simply looking to sum all numbers within the string. I only want to sum the numbers that come after my "\r\n". Updated example string in the OP. — boognish
– boognish, Commented Aug 6, 2018 at 6:16
Try regexp_matches(col,'(?:^|\n)\s*-\s*(\d*\.?\d+)','g'). If \n in the input is a 2 char combination, replace \n with \\n — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Aug 6, 2018 at 6:45

Wiktor Stribiżew · Accepted Answer · 2018-08-07 18:17:57Z

2

You may use

regexp_matches(col,'(?:^|\n)\s*-\s*(\d*\.?\d+)','g')

The part captured with (...) will be the output of the regexp_matches function.

Details

(?:^|\n) - start of string or newline
\s*-\s* - a hyphen enclosed with 0+ whitespaces
(\d*\.?\d+) - Capturing group 1 (what will be returned):
- \d* - 0+ digits
- \.? - 1 or 0 dots
- \d+ - 1+ digits.

answered Aug 7, 2018 at 18:17

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

Comments

boognish · Accepted Answer · 2018-08-06 06:38:50Z

1

This seems to work:

SELECT REGEXP_MATCHES( string::text ,'\Br\Bn ?- ?([0-9]+)', 'g') from test_table

answered Aug 6, 2018 at 6:38

boognish

508 bronze badges

Collectives™ on Stack Overflow

Postgres Parse String with Delimiter

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related