0

I have the following string from which I'm trying to extract only the text between the brackets and "AS Last_Partner_Shut_Down":

(SELECT     pth.ProgramId\n                       ,Last_Partner_Shut_Down_Date = MAX(pth.EventDate)\n             FROM       DW_Staging..Administration_Gen2_pay_programsTbl_History_Full AS pth\n             WHERE      pth.ProgramStatus = 1\n                        AND EXISTS (SELECT  1\n                                    FROM    DW_Staging..Administration_Gen2_pay_programsTbl_Full AS pt_in\n                                    WHERE   pt_in.ProgramId = pth.ProgramId\n                                            AND pt_in.ProgramStatus = 0)\n             GROUP BY   pth.ProgramId) AS Last_Partner_Shut_Down

When using "(\()(.*)(\))(.*)" in regex101 I get the desired groups:

enter image description here

But when running it in Python I get only "pth.EventDate" in group 2:

query = "(SELECT     pth.ProgramId\n                       ,Last_Partner_Shut_Down_Date = MAX(pth.EventDate)\n             FROM       DW_Staging..Administration_Gen2_pay_programsTbl_History_Full AS pth\n             WHERE      pth.ProgramStatus = 1\n                        AND EXISTS (SELECT  1\n                                    FROM    DW_Staging..Administration_Gen2_pay_programsTbl_Full AS pt_in\n                                    WHERE   pt_in.ProgramId = pth.ProgramId\n                                            AND pt_in.ProgramStatus = 0)\n             GROUP BY   pth.ProgramId) AS Last_Partner_Shut_Down"
sub_query = re.search("(\()(.*)(\)(.*))", query)
print(sub_query.group(2))

#Output: pth.EventDate

I imagine this has something to do with lazy/greedy quantifier but couldn't figured it out.

Thanks in advance.

Dina Kleper
  • 1,949
  • 4
  • 17
  • 23
  • Because the `.` does not match `\n`. Use `re.S` or `re.DOTALL` option to make `.` match across lines. When using regex101, make sure you use *literal text* and not *string literals* as the test text (see *`All languages - "Literal string" vs. "String literal" alert`* [here](https://stackoverflow.com/a/39636208/3832970)). – Wiktor Stribiżew Oct 07 '22 at 12:54

0 Answers0