Hi ,
I have a question. How do I replace 2 words in one line like this

IN CLO07 INDEX IN CLOIX07

to

IN CLO07_S02 INDEX IN CLOIX07_S02

But one thing to remember is that there are lots of words like CLODM001 .
So the only matching pattern is "IN CLO"

sample file is :

CREATE TABLE CLODM001.DIM_HOSPITAL (
HOSP_SYS_ID INTEGER NOT NULL ,
CONTR_MKT_NM VARCHAR(30) NOT NULL ,
CONTR_SUB_MKT_NM VARCHAR(30) NOT NULL
LOAD_DT DATE NOT NULL WITH DEFAULT CURRENT DATE ,
UPDT_DT DATE NOT NULL WITH DEFAULT CURRENT DATE )
COMPRESS YES
IN CLO07 INDEX IN CLOIX07 ;


I used this one but its not right right one .

sed -e 's/\(IN CLO[^"]*\)/\1_S02/g' db2look_DIM_HOSPITAL.ddl1 > db2look_DIM_HOSPITAL.ddl2

Thanks in advance ,

should perhaps work better.

Era,
It works fine for only one file. I am passing the many file for that change from CLO* to CLO*_S02 .
Here is the code part

if [ ${source_schema} = 'OPSDM001' ]
then
sed -e 's/\(TSO[^"]*\)/\1_S02/g' $TN.${ecmdate}.sql > $tmpfile_cln
sed -e 's/"//g' $tmpfile_cln > $TN.${ecmdate}.sql
elif [ ${source_schema} = 'DAYA' ]
then
sed -e 's/\(TEST[^"]*\)/\1_S02/g' $TN.${ecmdate}.sql > $tmpfile_cln
sed -e 's/"//g' $tmpfile_cln > $TN.${ecmdate}.sql
elif [ ${source_schema} = 'CLODM001' ]
then
sed -e 's/\(IN CLO[^ ]*\)/\1_S02/g' $TN.${ecmdate}.sql > $tmpfile_cln
sed -e 's/"//g' $tmpfile_cln > $TN.${ecmdate}.sql
elif [ ${source_schema} = 'UHCDM001' ]
then
sed -e 's/\(TSHL[^"]*\)/\1_S02/g' $TN.${ecmdate}.sql > $tmpfile_cln
sed -e 's/"//g' $tmpfile_cln > $TN.${ecmdate}.sql
else
echo " hi "
fi

This script works fine for other schemas but CLODM001 is not working at all. Am I missing something here too ?

Thanks for yoor help !

Where are the double quotes coming from, you are removing double quotes after the basic substitution, which suggests that something is adding double quotes somewhere earlier in the script. Then maybe the substitution should be different, too. If all the others are working then maybe you need to have ^" there after all. Or if you have double quotes in or around "IN" too then obviously they need to be taken into account.

You are right Era ,
I moved the file cleaning before CLO part and it works fine now .

Thanks !