1

Hi I have this query and I would like to extract exactly the t0_0.pp,0 that is in COALESCENSE() parenthesis. I can have more parethesis before or after the COALESCENSE for this I am trying to use regular expressions like this (([^()]+))' but is givving me all inside all the parenthesis and I only wnat the COALESCENSE. Any idea?

select DISTINCT SUM(case when t0_0.pp is not null  then COALESCE(t0_0.pp,0)
else 0 end) from inventory_coverage_view t0_0 where  LOWER(t0_0.h) ='vg' AND t0_0.id = '9'

thank you in advance :)

Joan Triay
  • 1,518
  • 6
  • 20
  • 35

1 Answers1

3

To parse arbitrary SQL queries, you really need a specific parser.

If you know for sure there can be no other ) inside the COALESCE parentheses, you may update your regex in the following way:

import re
pat = r"COALESCE\((.*?)\)"
s = "select DISTINCT SUM(case when t0_0.pp is not null  then COALESCE(t0_0.pp,0)\nelse 0 end) from inventory_coverage_view t0_0 where  LOWER(t0_0.h) ='vg' AND t0_0.id = '9'"
m = re.search(pat, s)
if m:
    print(m.group(1)) # => t0_0.pp,0

See the regex demo and a Python demo.

  • COALESCE\( - matches the COALESCE( literal substring
  • (.*?) - captures any 0+ chars other than line break chars, as few as possible, into Group 1
  • \) - matches a literal ).
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • Is there a reason you use `.*?` instead of `[^)]*`? – Sebastian Proske Jun 30 '17 at 13:54
  • 1
    @SebastianProske: Yes. In order not to overflow to the next line. I explained the pattern: *`.*?` ...any 0+ chars other than line break chars*. If there may be line breaks, then `[^)]*` is the natural solution. With questions based on assumptions, these nuances are not relevant (we just do not see the real data OP has). The main point here was adding left-hand side context. Besides, I think OP will have to precise the right-hand side boundary with more patterns, and then `.*?` will be more handy. To match line breaks, the `re.S` flag can be passed: `re.search(pat, s, flags=re.S)` – Wiktor Stribiżew Jun 30 '17 at 14:00
  • I see, thanks. Please don't see my question as criticism, I was just curious. – Sebastian Proske Jun 30 '17 at 14:10