#include <StringConstants.au3> ; to declare the Constants of StringRegExp
#include <Array.au3> ; UDF needed for _ArrayDisplay and _ArrayConcatenate
Local $sRegex = "(?i)(?:(?:from|join)(?:\s|\()+((?:\w*\.\w*)+)(?:\s|\))+)"
Local $sString = "SELECT T.IDP_WAREHOUSE_ID" & @CRLF & _
" ,T.IDP_AUDIT_ID" & @CRLF & _
" ,T.IDP_DATA_ID" & @CRLF & _
" ,T.IDP_DATA_DATE" & @CRLF & _
" ,T.TRADESYSTEM" & @CRLF & _
" ,T.SOURCESYSTEM" & @CRLF & _
" ,T.SUBSYSTEM" & @CRLF & _
" ,T.TRADEDATE" & @CRLF & _
" ,T.VALUATIONDATE" & @CRLF & _
" ,T.TRADEID" & @CRLF & _
" ,T.TRADETYPE" & @CRLF & _
" ,T.TRADESTATUS" & @CRLF & _
" ,T.MAPPEDTRADESTATUS" & @CRLF & _
" ,T.RAWBOOKINGLOCATION" & @CRLF & _
" ,T.MAPPEDBOOKINGLOCATION" & @CRLF & _
" ,T.BOOKINGLOCREGION" & @CRLF & _
" ,T.CMCIFBOOKINGLOCATION" & @CRLF & _
" ,T.CMCIFBOOKINGLOCATIONREGION" & @CRLF & _
" ,T.COUNTERPARTYACCOUNTID" & @CRLF & _
" ,T.COUNTERPARTYSHORTNAME" & @CRLF & _
" ,T.COUNTERPARTYTYPE" & @CRLF & _
" ,T.CUSTOMERNAME" & @CRLF & _
" ,T.CUSTOMERADDRESS" & @CRLF & _
" ,T.CUSTOMERCOUNTRYCODE" & @CRLF & _
" ,T.CUSTOMERCOUNTRYNAME" & @CRLF & _
" ,T.CAMLO_RISK" & @CRLF & _
" ,T.US_BSA_RISK" & @CRLF & _
" ,T.RISKRATING" & @CRLF & _
" ,T.PRODUCTTYPE" & @CRLF & _
" ,T.PRODUCT" & @CRLF & _
" ,T.CMCIFPRODUCTTYPE" & @CRLF & _
" ,T.QUANTITY" & @CRLF & _
" ,T.PRICE" & @CRLF & _
" ,T.CURRENCY" & @CRLF & _
" ,T.CURRENCY_ISO_CODE" & @CRLF & _
" ,T.CURRENCY_DESCRIPTION" & @CRLF & _
" ,T."VALUE"" & @CRLF & _
" ,T.TRADERID" & @CRLF & _
" ,T.TRANSACTIONACTIVITY" & @CRLF & _
" ,T.SOURCETRADEID" & @CRLF & _
" ,T.TRADESEQUENCENUMBER" & @CRLF & _
" ,T.BASECURRENCY" & @CRLF & _
" ,T.BASECURRPRICE" & @CRLF & _
" ,T.BASECURRQUANTITY" & @CRLF & _
" ,T.BASECURRVALUE" & @CRLF & _
" ,T.ISIN" & @CRLF & _
" ,T.CUSIP" & @CRLF & _
" ,T.RESP" & @CRLF & _
" ,T.FACILITY_GLOBAL_COMMITMENT" & @CRLF & _
" ,T.FACILITY_HOST_BANK_NET_AMT" & @CRLF & _
" ,T.MAP_TRADESYSTEM" & @CRLF & _
" ,T.MAP_ACCOUNTFIELDNAME" & @CRLF & _
" ,T.MAP_COUNTERPARTYACCOUNTID" & @CRLF & _
" ,T.MAP_CUID" & @CRLF & _
" ,T.MAP_CIF" & @CRLF & _
" ,T.MAP_UEN" & @CRLF & _
" ,RC.RAW AS CHICAGO_RAW" & @CRLF & _
" ,RC.RESPONSIBILITY_CENTER AS CHICAGO_RESPONSIBILITY_CENTER" & @CRLF & _
" ,RC.DESK AS CHICAGO_DESK" & @CRLF & _
" ,RC.BUSINESS_SEGMENT AS CHICAGO_BUSINESS_SEGMENT" & @CRLF & _
" ,RC.LOB AS CHICAGO_LOB" & @CRLF & _
" ,RC.OPERATING_GROUP AS CHICAGO_OPERATING_GROUP" & @CRLF & _
" ,AFF.RAW AS AFF_RAW" & @CRLF & _
" ,ISS.RAW AS ISS_RAW" & @CRLF & _
" ,EC1.ENTITYCOUNTRY AS REGISTRATION_COUNTRY" & @CRLF & _
" ,EC2.ENTITYCOUNTRY AS PRINCIPAL_PLACE_OF_BUSINESS" & @CRLF & _
" ,ENT.CIF AS ENT_CIF" & @CRLF & _
" ,ENT.UEN AS ENT_UEN" & @CRLF & _
" ,ENT.ENTITYLEGALNAME AS ENT_ENTITYLEGALNAME" & @CRLF & _
" ,ENT.ENTITYADDRESS AS ENT_ENTITYADDRESS" & @CRLF & _
" ,ENT.ENTITYACCOUNTNUMBER AS ENT_ENTITYACCOUNTNUMBER" & @CRLF & _
" ,ENT.ENTITYSTATUS AS ENT_ENTITYSTATUS" & @CRLF & _
" ,ENT.IFI_FLAG AS ENT_IFI_FLAG" & @CRLF & _
" ,ENT.SICCAN AS ENT_SICCAN" & @CRLF & _
" ,ENT.SICUS AS ENT_SICUS" & @CRLF & _
" ,ENT.NBFI_312 AS ENT_NBFI_312" & @CRLF & _
" ,ENT.NBFI_NON_312 AS ENT_NBFI_NON_312" & @CRLF & _
" ,ENT.RMA AS ENT_RMA" & @CRLF & _
" ,ENT.SICCAN_DESC AS ENT_SICCAN_DESC" & @CRLF & _
" ,ENT.SICUS_DESC AS ENT_SICUS_DESC" & @CRLF & _
" ,ENT.P_AND_C AS ENT_P_AND_C" & @CRLF & _
" ,ENT.OFAC AS ENT_OFAC" & @CRLF & _
" ,ENT.RMA_ONLY AS ENT_RMA_ONLY" & @CRLF & _
" ,ENT.CONN_UEN AS ENT_CONN_UEN" & @CRLF & _
" ,ENT.CM_INDICATOR AS ENT_CM_INDICATOR" & @CRLF & _
" ,ENT.BMO_RESP AS ENT_BMO_RESP" & @CRLF & _
" ,ENT.HARRIS_RESP AS ENT_HARRIS_RESP" & @CRLF & _
" ,ENT.PRIM_IND AS ENT_PRIM_IND" & @CRLF & _
" ,ENT.PM_CODE AS ENT_PM_CODE" & @CRLF & _
" ,APPR.CUSTOMER_NAME AS APPR_CUSTOMER_NAME" & @CRLF & _
" ,APPR.UEN AS APPR_UEN" & @CRLF & _
" ,APPR.COUNTRY AS APPR_COUNTRY" & @CRLF & _
" ,APPR.CONDITIONAL_APPROVAL AS APPR_CONDITIONAL_APPROVAL" & @CRLF & _
" ,APPR.PRODUCTS AS APPR_PRODUCTS" & @CRLF & _
" ,APPR_FI.CUSTOMER_NAME AS APPR_FI_CUSTOMER_NAME" & @CRLF & _
" ,APPR_FI.UEN AS APPR_FI_UEN" & @CRLF & _
" ,APPR_FI.COUNTRY AS APPR_FI_COUNTRY" & @CRLF & _
" ,APPR_FI.CONDITIONAL_APPROVAL AS APPR_FI_CONDITIONAL_APPROVAL" & @CRLF & _
" ,APPR_FI.PRODUCTS AS APPR_FI_PRODUCTS" & @CRLF & _
" ,APMS.UEN AS APMS_UEN" & @CRLF & _
" ,APMS.CIF AS APMS_CIF" & @CRLF & _
" ,APMS.ENTITY_LEGAL_NAME AS APMS_ENTITY_LEGAL_NAME" & @CRLF & _
" ,APMS.ENTITY_SHORT_NAME AS APMS_ENTITY_SHORT_NAME" & @CRLF & _
" ,APMS.OWNERSHIP_CLASS AS APMS_OWNERSHIP_CLASS" & @CRLF & _
" ,APMS.ENTITY_CLASSIFICATION AS APMS_ENTITY_CLASSIFICATION" & @CRLF & _
" ,APMS.ASSET_CLASSIFICATION AS APMS_ASSET_CLASSIFICATION" & @CRLF & _
" ,APMS.BMO_RC AS APMS_BMO_RC" & @CRLF & _
" ,APMS.ENTITY_TYPE AS APMS_ENTITY_TYPE" & @CRLF & _
" ,APMS.TIN_SSN AS APMS_TIN_SSN" & @CRLF & _
"FROM (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" SELECT A.IDP_WAREHOUSE_ID" & @CRLF & _
" ,A.IDP_AUDIT_ID" & @CRLF & _
" ,A.IDP_DATA_ID" & @CRLF & _
" ,A.IDP_DATA_DATE" & @CRLF & _
" ,A.TRADESYSTEM" & @CRLF & _
" ,A.SOURCESYSTEM" & @CRLF & _
" ,A.SUBSYSTEM" & @CRLF & _
" ,A.TRADEDATE" & @CRLF & _
" ,A.VALUATIONDATE" & @CRLF & _
" ,A.TRADEID" & @CRLF & _
" ,A.TRADETYPE" & @CRLF & _
" ,A.TRADESTATUS" & @CRLF & _
" ,A.MAPPEDTRADESTATUS" & @CRLF & _
" ,A.RAWBOOKINGLOCATION" & @CRLF & _
" ,A.MAPPEDBOOKINGLOCATION" & @CRLF & _
" ,A.BOOKINGLOCREGION" & @CRLF & _
" ,A.CMCIFBOOKINGLOCATION" & @CRLF & _
" ,A.CMCIFBOOKINGLOCATIONREGION" & @CRLF & _
" ,A.COUNTERPARTYACCOUNTID" & @CRLF & _
" ,A.COUNTERPARTYSHORTNAME" & @CRLF & _
" ,A.COUNTERPARTYTYPE" & @CRLF & _
" ,A.CUSTOMERNAME" & @CRLF & _
" ,A.CUSTOMERADDRESS" & @CRLF & _
" ,A.CUSTOMERCOUNTRYCODE" & @CRLF & _
" ,A.CUSTOMERCOUNTRYNAME" & @CRLF & _
" ,A.CAMLO_RISK" & @CRLF & _
" ,A.US_BSA_RISK" & @CRLF & _
" ,A.RISKRATING" & @CRLF & _
" ,A.PRODUCTTYPE" & @CRLF & _
" ,A.PRODUCT" & @CRLF & _
" ,A.CMCIFPRODUCTTYPE" & @CRLF & _
" ,A.QUANTITY" & @CRLF & _
" ,A.PRICE" & @CRLF & _
" ,A.CURRENCY" & @CRLF & _
" ,A.CURRENCY_ISO_CODE" & @CRLF & _
" ,A.CURRENCY_DESCRIPTION" & @CRLF & _
" ,A."VALUE"" & @CRLF & _
" ,A.TRADERID" & @CRLF & _
" ,A.TRANSACTIONACTIVITY" & @CRLF & _
" ,A.SOURCETRADEID" & @CRLF & _
" ,A.TRADESEQUENCENUMBER" & @CRLF & _
" ,A.BASECURRENCY" & @CRLF & _
" ,A.BASECURRPRICE" & @CRLF & _
" ,A.BASECURRQUANTITY" & @CRLF & _
" ,A.BASECURRVALUE" & @CRLF & _
" ,A.ISIN" & @CRLF & _
" ,A.CUSIP" & @CRLF & _
" ,A.RESP" & @CRLF & _
" ,A.FACILITY_GLOBAL_COMMITMENT" & @CRLF & _
" ,A.FACILITY_HOST_BANK_NET_AMT" & @CRLF & _
" ,MAP.TRADESYSTEM AS MAP_TRADESYSTEM" & @CRLF & _
" ,MAP.ACCOUNTFIELDNAME AS MAP_ACCOUNTFIELDNAME" & @CRLF & _
" ,MAP.COUNTERPARTYACCOUNTID AS MAP_COUNTERPARTYACCOUNTID" & @CRLF & _
" ,MAP.CUID AS MAP_CUID" & @CRLF & _
" ,MAP.CIF AS MAP_CIF" & @CRLF & _
" ,CASE " & @CRLF & _
" WHEN (A.TRADESYSTEM = 'LIQ'::"VARCHAR")" & @CRLF & _
" THEN A.COUNTERPARTYACCOUNTID" & @CRLF & _
" ELSE ("VARCHAR" (MAP.UEN))::VARCHAR(16)" & @CRLF & _
" END AS MAP_UEN" & @CRLF & _
" FROM (" & @CRLF & _
" BLUEHOUSE.V_TRANSACTIONS A LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITYMAPPING MAP ON (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" (A.COUNTERPARTYACCOUNTID = UPPER(BTRIM(MAP.COUNTERPARTYACCOUNTID)))" & @CRLF & _
" AND (UPPER(A.TRADESYSTEM) = UPPER(MAP.TRADESYSTEM))" & @CRLF & _
" )" & @CRLF & _
" AND (" & @CRLF & _
" MAP.IDP_DATA_ID = (" & @CRLF & _
" SELECT MAX(IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITYMAPPING.IDP_DATA_ID) AS MAX" & @CRLF & _
" FROM IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITYMAPPING" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" ) T LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_REF_CHICAGO_RCMAPPING RC ON (" & @CRLF & _
" (" & @CRLF & _
" (RC.RAW = T.RESP)" & @CRLF & _
" AND (RC.IDP_END_DATE = '2099-12-31'::DATE)" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" ) LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_REF_CHICAGO_AFFILIATED_RCS AFF ON (" & @CRLF & _
" (" & @CRLF & _
" (AFF.RAW = T.RESP)" & @CRLF & _
" AND (AFF.IDP_END_DATE = '2099-12-31'::DATE)" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" ) LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_REF_CHICAGO_BOOKING_ISSUES_RCS ISS ON (" & @CRLF & _
" (" & @CRLF & _
" (ISS.RAW = T.RESP)" & @CRLF & _
" AND (ISS.IDP_END_DATE = '2099-12-31'::DATE)" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" ) LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIESCOUNTRY EC1 ON (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" EC1.IDP_DATA_ID = (" & @CRLF & _
" SELECT MAX(IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIESCOUNTRY.IDP_DATA_ID) AS MAX" & @CRLF & _
" FROM IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIESCOUNTRY" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" AND (("VARCHAR" (EC1.UEN))::VARCHAR(16) = T.MAP_UEN)" & @CRLF & _
" )" & @CRLF & _
" AND (UPPER(EC1.ENTITYADDRESSTYPE) = 'REGISTERED'::"VARCHAR")" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" ) LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIESCOUNTRY EC2 ON (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" EC2.IDP_DATA_ID = (" & @CRLF & _
" SELECT MAX(IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIESCOUNTRY.IDP_DATA_ID) AS MAX" & @CRLF & _
" FROM IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIESCOUNTRY" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" AND (("VARCHAR" (EC2.UEN))::VARCHAR(16) = T.MAP_UEN)" & @CRLF & _
" )" & @CRLF & _
" AND (UPPER(EC2.ENTITYADDRESSTYPE) = 'PRINCIPAL PLACE OF BUSINESS'::"VARCHAR")" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" ) LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIES ENT ON (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" ENT.IDP_DATA_ID = (" & @CRLF & _
" SELECT MAX(IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIES.IDP_DATA_ID) AS MAX" & @CRLF & _
" FROM IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_ENTITIES" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" AND (("VARCHAR" (ENT.UEN))::VARCHAR(16) = T.MAP_UEN)" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" ) LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APPROVED_US_312 APPR ON (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" APPR.IDP_DATA_ID = (" & @CRLF & _
" SELECT MAX(IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APPROVED_US_312.IDP_DATA_ID) AS MAX" & @CRLF & _
" FROM IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APPROVED_US_312" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" AND (("VARCHAR" (APPR.UEN))::VARCHAR(16) = T.MAP_UEN)" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" ) LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APPROVED_US_FI APPR_FI ON (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" APPR_FI.IDP_DATA_ID = (" & @CRLF & _
" SELECT MAX(IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APPROVED_US_FI.IDP_DATA_ID) AS MAX" & @CRLF & _
" FROM IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APPROVED_US_FI" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" AND (("VARCHAR" (APPR_FI.UEN))::VARCHAR(16) = T.MAP_UEN)" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" ) LEFT JOIN IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APMS_ENTITIES APMS ON (" & @CRLF & _
" (" & @CRLF & _
" (" & @CRLF & _
" APMS.IDP_DATA_ID = (" & @CRLF & _
" SELECT MAX(IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APMS_ENTITIES.IDP_DATA_ID) AS MAX" & @CRLF & _
" FROM IDP_PRD_LEVEL1.BLUEHOUSE.L1_WRK_APMS_ENTITIES" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" AND (("VARCHAR" (APMS.UEN))::VARCHAR(16) = T.MAP_UEN)" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
" )" & @CRLF & _
"WHERE (" & @CRLF & _
" (UPPER(T.CMCIFBOOKINGLOCATION) ~ ~ LIKE_ESCAPE('%CHICAGO%'::"VARCHAR", '\'::"VARCHAR"))" & @CRLF & _
" OR (T.RESP = AFF.RAW)" & @CRLF & _
" );" & @CRLF & _
""
Local $aArray = StringRegExp($sString, $sRegex, $STR_REGEXPARRAYGLOBALFULLMATCH)
Local $aFullArray[0]
For $i = 0 To UBound($aArray) -1
_ArrayConcatenate($aFullArray, $aArray[$i])
Next
$aArray = $aFullArray
; Present the entire match result
_ArrayDisplay($aArray, "Result")
Please keep in mind that these code samples are automatically generated and are not guaranteed to work. If you find any syntax errors, feel free to submit a bug report. For a full regex reference for AutoIt, please visit: https://www.autoitscript.com/autoit3/docs/functions/StringRegExp.htm