I’m working on a query where I need to parse Description field that follow the pattern “userid-date-amount-” For example, a valid value would be: 222826-2022/07/26-32,700,000-
However, my current implementation also extracts values like the following:
Tracking code: 1160685022- Date: 2022/07/26- Time: 21:02- Last 4 digits: None- First 4 digits: None- Amount: 350 thousand Dollars- To Paypal
I want to modify my parsing logic to only consider values that strictly adhere to the “userid-date-amount-” format.
SELECT
i."Id",
i."Description",
unnest(regexp_matches(i."Description", '(\d+)-(.*?)-(.*)-')) AS extracted_data
FROM "public"."Invoices" i
I also need to break down the original value into three separate fields like this: 222826, 2022/07/26, and 32,700,000.
You could make the pattern more specific and match the date like pattern with digits and forward slashes.
For the amount you could start with a single digits and match optional digits or comma's (Or make even that part more specific according to the allowed format)
(\d+)-(\d{4}/\d\d/\d\d)-(\d[\d,]*)-
See a regex demo
If the leading digits should not be a partial word match, you could start with a word boundary \y(\d+)