How to remove a char before a pattern?

Hi I have a file where i want to remove a char before a specific pattern.
exp:

CREATE TABLE
 (
 A,
 B,
 C,      ----comma needs to be removed
 )AS SELECT
 A,
 B,
 C,      ----comma needs to be removed
 FROM  TABLE.

So i want to delete the comma(,) after the C both ways.Pattern can be look for ")AS SELECT" and "FROM" .So before this pattern i want to delete a single char which is comma.
my output should:

CREATE TABLE
 (
 A,
 B,
 C      -----removed comma
 )AS SELECT
 A,
 B,
 C      -----removed comma
 FROM  TABLE.

Hello raju2016,

Kindly use code tags for commands/codes/Input_file as per forum rules. Could you please try following and let us know if this helps.

awk '{sub(/^C,/,"C");print}'   Input_file

Thanks,
R. Singh

Hi Ravinder,

The column "C" is not always constant.So thats why i said based on the pattern before the one character which is comma(,) need to be removed.MAy be sometime more columns come..

Hello raju2016,

Could you please post sample Input_file(with more clear samples please) and expected output as it is not clear.

Thanks,
R. Singh

Input:

/*in below example comma needs to remove after C*/
CREATE TABLE
(
A,
B,
C,    ----comma needs to be removed
)AS SELECT
A,
B,
C,     ----comma needs to be removed
FROM TABLE

/*in below example comma needs to remove after D*/
CREATE TABLE
(
A,
B,
C,
D,     ----comma needs to be removed
)AS SELECT
A,
B,
C,    
D,      ----comma needs to be removed
FROM TABLE.

Expected O/P

/*comma removed from C in both place*/
CREATE TABLE
(
A,
B,
C          -----comma removed
)AS SELECT
A,
B,
C             -----comma removed
FROM TABLE;
/*comma replaced from D */
CREATE TABLE
(
A,
B,
C,
D        ----comma  removed
)AS SELECT
A,
B,
C,    
D        ----comma  removed
FROM TABLE3.

you can use pattern because the pattern is constant...like "FROM" and ")AS SELECT". Need to remove the comma before these two patterns.

Hello raju2016,

Could you please try following and let us know if this helps you.

awk '/AS SELECT/ || /FROM TABLE/{sub(/,$/,"",VAL);print VAL;VAL=$0;next} VAL{print VAL} {;VAL=$0} END{print VAL}'  Input_file
 

Output will be as follows.

CREATE TABLE
(
A,
B,
C
)AS SELECT
A,
B,
C
FROM TABLE
CREATE TABLE
(
A,
B,
C,
D
)AS SELECT
A,
B,
C,
D
FROM TABLE.
 

Thanks,
R. Singh

Hello raju2016,

You could thank a person by hitting THANKS button, also please use code tags for sample Input_file. Could you please try following once.

awk '/^)$/{sub(/,$/,"",VAL);print VAL;VAL=$0;next} VAL{print VAL} {;VAL=$0} END{print VAL}'   Input_file

Thanks,
R. Singh

Or, a bit more precise (regarding empty lines and empty file)

awk '/AS SELECT/ || /FROM TABLE/ {sub(/,$/,"",VAL)} NR>1{print VAL} {VAL=$0} END {if (NR) print VAL}'  Input_file

perl -07 -pe 's/,(\nF|\n\))/$1/gs' raju2016.input

@Ravindra:The code for the 2nd scenario where i need to extract all the code between the parenthesis is not working

Are we already discussing this issue here: Extract data between two parenthesi

Please do not duplicate questions into new threads.