Extract from string using regex in SQL (Snowflake)

Question

I have a database field which contains a long string of text:

Date: 02/03/22 Customer Price: 500 Total Price: 701.56 Paid to Date: 304.10 Confirmed: TRUE

What would be the best way of extracting out certain numbers from this string? For example, how could I extract 'Total Price' (701.56) and 'Paid to Date' (304.10)? I think I need to use REGEXP_SUBSTR but not I'm 100% sure. For example:

select REGEXP_SUBSTR(my_field_name, 'Total Price: (SOME-REGEX)') as total_price,
select REGEXP_SUBSTR(my_field_name, 'Paid To Date: (SOME-REGEX)') as paid_to_date,
from my_table

I only need to return the values, not the preceding text.

Sergiu · Accepted Answer · 2022-03-02 16:03:57Z

2

Here is one way of doing it:

set my_string='Date: 02/03/22 Customer Price: 500 Total Price: 701.56 Paid to Date: 304.10 Confirmed: TRUE';
select REGEXP_SUBSTR($my_string, 'Total Price: (\\d*.\\d*)',1,1,'e') as total_price;
select REGEXP_SUBSTR($my_string, 'Paid to Date: (\\d*.\\d*)',1,1,'e') as paid_to_date;

Results:

701.56
304.10

answered Mar 2, 2022 at 16:03

Sergiu

4,7661 gold badge16 silver badges23 bronze badges

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

Comments

Anand · Accepted Answer · 2022-03-02 16:06:32Z

1

SELECT 'Date: 02/03/22 Customer Price: 500 Total Price: 701.56 Paid to Date: 304.10 Confirmed: TRUE' as a , 
REGEXP_SUBSTR(a,'Total Price: [0-9]*.[0-9]*') as total_price,
REGEXP_SUBSTR(a,'Paid to Date: [0-9]*.[0-9]*') as paid_to_date;

edited Mar 2, 2022 at 16:06

answered Mar 2, 2022 at 16:01

Anand

5743 silver badges6 bronze badges

Collectives™ on Stack Overflow

Extract from string using regex in SQL (Snowflake)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related