Replace a string before a certain line

Using GNU sed:

sed  'N;s/,\(\s*\n.*PRIMARY\)/)\1/;P;D' file

ID_SOUR_CALENDAR BIGINT NOT NULL DEFAULT 0  COMPRESS 0 )
UNIQUE PRIMARY INDEX ( CALENDAR_DATE );
ID ,
ID_SOUR )
PRIMARY INDEX ( CALENDAR_DATE );

• N Read/append the next line of input into the pattern space.
• P Print up to the first embedded newline of the current pattern space.
• D Delete up to the first embedded newline in the pattern space. Start next cycle, but skip reading from the input if there is still data in the pattern space.

Try reversing the file, doing the string replacement on the line after PRIMARY, then re-reversing the file:

tac file | sed '/PRIMARY/ {n; s/,$/)/}' | tac

try this,

perl -0777 -pe 's/,([^\n,]*\n[^\n]*PRIMARY)/)$1/g'

This will replace the last , with ) in lines before any line including PRIMARY.

Explanation

• perl -0777 slurp whole file (read as one line)
• s/search_pattern/replacement/g Substitute search pattern with replacement globally