How to extract words between 2 parentheses in Unix

问题:

I have a SAS dataset with the list of queries as one of the variable. Below is one of the variable value:

SELECT * FROM ( SELECT Table1 file2.txt file.txt QUEUES QDefinitions
Parameters TRAP-Deposit-DSTran.dat.2016-08-07 FROM CS_CASE WHERE 
ANT_CD='FI_BASE_TENANT')t1 LEFT OUTER JOIN Table2 t2 ON 
t2.CASE_ID=t1.CASE_ID LEFT OUTER JOIN Table3 t3 ON 
t3.SERVICE_XID=t1.SERVICE_XID LEFT OUTER JOIN Table4 t4 ON 
t4.SERVICE_ID=t1.SERVICE_ID WHERE ( t1.CASESTATUs_CD = (NEW) OR 
t1.CASE_STATUS_CD = (OPEN) )
AND ( t3.SEARCH_VALUE = (HighVal_Ind) AND t3.SEARCH_VALUE_N <= 3 ) AND 
( t4.SEARCH_VALUE_Nm = (Curr_Strategy) AND t4.SEARCH_STRG = (095) ) 
GROUP BY t1.CASE_REFERENCE,t2.LAST_SCRFP,t1.SERVICE_ID 
ORDER BY t2.LAST_SCRFP DESC

What I want to do is I want to put the value enter code here's in () as (' ').

For example:

WHERE ( t1.CASESTATUs_CD = (NEW) OR t1.CASE_STATUS_CD = (OPEN) ) 

I want to change this to:

WHERE ( t1.CASESTATUs_CD = ('NEW') OR t1.CASE_STATUS_CD = ('OPEN') ) 

How can I do it?

回答1:

Your question isn't clear but MAYBE this is what you want:

$ sed -E 's/\(([^()]+)\)/('\''\1'\'')/g' file
SELECT * FROM ( SELECT Table1 file2.txt file.txt QUEUES QDefinitions
Parameters TRAP-Deposit-DSTran.dat.2016-08-07 FROM CS_CASE WHERE
ANT_CD='FI_BASE_TENANT')t1 LEFT OUTER JOIN Table2 t2 ON
t2.CASE_ID=t1.CASE_ID LEFT OUTER JOIN Table3 t3 ON
t3.SERVICE_XID=t1.SERVICE_XID LEFT OUTER JOIN Table4 t4 ON
t4.SERVICE_ID=t1.SERVICE_ID WHERE ( t1.CASESTATUs_CD = ('NEW') OR
t1.CASE_STATUS_CD = ('OPEN') )
AND ( t3.SEARCH_VALUE = ('HighVal_Ind') AND t3.SEARCH_VALUE_N <= 3 ) AND
( t4.SEARCH_VALUE_Nm = ('Curr_Strategy') AND t4.SEARCH_STRG = ('095') )
GROUP BY t1.CASE_REFERENCE,t2.LAST_SCRFP,t1.SERVICE_ID
ORDER BY t2.LAST_SCRFP DESC

The above was run on this input file:

$ cat file
SELECT * FROM ( SELECT Table1 file2.txt file.txt QUEUES QDefinitions
Parameters TRAP-Deposit-DSTran.dat.2016-08-07 FROM CS_CASE WHERE
ANT_CD='FI_BASE_TENANT')t1 LEFT OUTER JOIN Table2 t2 ON
t2.CASE_ID=t1.CASE_ID LEFT OUTER JOIN Table3 t3 ON
t3.SERVICE_XID=t1.SERVICE_XID LEFT OUTER JOIN Table4 t4 ON
t4.SERVICE_ID=t1.SERVICE_ID WHERE ( t1.CASESTATUs_CD = (NEW) OR
t1.CASE_STATUS_CD = (OPEN) )
AND ( t3.SEARCH_VALUE = (HighVal_Ind) AND t3.SEARCH_VALUE_N <= 3 ) AND
( t4.SEARCH_VALUE_Nm = (Curr_Strategy) AND t4.SEARCH_STRG = (095) )
GROUP BY t1.CASE_REFERENCE,t2.LAST_SCRFP,t1.SERVICE_ID
ORDER BY t2.LAST_SCRFP DESC

回答2:

This is best solved using regular expression. In SAS this is done using the prxchange function. Assuming you have no whitespace in your conditions, we check for brackets with no whitespace in between. Adjust the function if there are other conditions.

data query;
    length want $3000;
    have= 
        "SELECT * FROM ( SELECT Table1 file2.txt file.txt QUEUES QDefinitions
        Parameters TRAP-Deposit-DSTran.dat.2016-08-07 FROM CS_CASE WHERE 
        ANT_CD='FI_BASE_TENANT')t1 LEFT OUTER JOIN Table2 t2 ON 
        t2.CASE_ID=t1.CASE_ID LEFT OUTER JOIN Table3 t3 ON 
        t3.SERVICE_XID=t1.SERVICE_XID LEFT OUTER JOIN Table4 t4 ON 
        t4.SERVICE_ID=t1.SERVICE_ID WHERE ( t1.CASESTATUs_CD = (NEW) OR 
        t1.CASE_STATUS_CD = (OPEN) )
        AND ( t3.SEARCH_VALUE = (HighVal_Ind) AND t3.SEARCH_VALUE_N <= 3 ) AND 
        ( t4.SEARCH_VALUE_Nm = (Curr_Strategy) AND t4.SEARCH_STRG = (095) ) 
        GROUP BY t1.CASE_REFERENCE,t2.LAST_SCRFP,t1.SERVICE_ID 
        ORDER BY t2.LAST_SCRFP DESC";
    want =  prxchange("s/\(([^ ]+)\)/('\1')/", -1, have); 
run;