Extracting information using regex

Question

I am new to SQL and need to query a database to extract certain information before I can import it into another software I am familiar with to analyse the data. The table I am trying to query has information that looks like below:

MV: Gone Girl (2014)

BT: USD 61,000,000 

CP: Twentieth Century Fox Film Corporation, Regency Entertainment (USA), Inc. 

GR: USD 167,735,396 (USA) (8 February 2015) 

GR: USD 167,590,676 (USA) (25 January 2015) 

GR: USD 37,513,109 (USA) (5 October 2014) 

GR: USD 167,761,501 (USA)

I would like to extract the information in the lines that start with GR, and I would like to organize them into four columns;

currency,
amount,
country,
date.

After spending a lot of time I have now put together the following code (I know it is not an elegant way of doing it), but it does not grab the information in the last line because it is missing the date information. I would like the date column to be empty for the last row, but still extract all the other information.

regex_match '(?:GR:[ ]([A-Z]{3})[ ](\d{1,3}(?:[,]\d{3})+)[ ][(](USA)[)][ ][(](?:|\d{1,2}[ ]\w+[ ]\d{1,4})){1}','g')

I would be grateful if someone could help me to fix my code.

You should post an actual example of the code you're having a problem with. — Broken Man
– Broken Man, Commented Nov 20, 2015 at 11:03

naurel · Accepted Answer · 2015-11-20 11:20:55Z

1

This might do what you want even if I'm not sure it's what you need it's at least what you asked :

(?:GR: )([A-Z]{3}) ((?:[0-9]{1,3},*)*) (?:\(([A-Z]{3})\)) *(?:\(([1-9]{1,2} [a-zA-Z]* [0-9]{4})\))*

You can check here to see the result.

You have 4 groups one for each of your column. And sometimes the fourth can be empty (if there is no date for example).

answered Nov 20, 2015 at 11:20

naurel

6254 silver badges18 bronze badges

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

1 Comment

naurel Over a year ago

Accept the solution then.

Collectives™ on Stack Overflow

Extracting information using regex

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related