fixing with sed

Linux and Unix-Like Linux
1

I am trying to replace the value of $f3 but its not working . I don't know what I am missing here .

 cat dim_copy.20080516.sql | grep -i "create view" | grep -v OPSDM002 |  while read f1 f2 f3 f4 f5 f6 f7 f8 f9   
       do
              echo " $f3 " 
             sed -e s/"${f3}"/OPSDM002."${f3}"/ dim_copy.20080516.sql > dim_copy.20080516.sql1
         done

Can someone suggest me how to fix it .

Thanks in advance

2

Post some lines of the sql file and the desired output.

Regards

3

The SQL file is

CREATE VIEW MOUSE_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR
FROM OPSDM002.DIM_COPY;
CREATE VIEW OPSDM002.TABLE_UHC AS SELECT DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR,LOAD_DT,WEEK_NBR_YR,SEQ_NBR
FROM OPSDM002.DIM_COPY;
CREATE VIEW RAT_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR

The desired output is

CREATE VIEW OPSDM002.MOUSE_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR
FROM OPSDM002.DIM_COPY;
CREATE VIEW OPSDM002.TABLE_UHC AS SELECT DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR,LOAD_DT,WEEK_NBR_YR,SEQ_NBR
FROM OPSDM002.DIM_COPY;
CREATE VIEW OPSDM002.RAT_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR

Thanks

4

If I understand correctly what you try to do:

sed -r '/OPSDM002/! s/CREATE VIEW ([^ ]+)/CREATE VIEW OPSDM002.\1/' your_file
5

Ripat ,
When I run your command i get the following error

#sed -r '/OPSDM002/! s/CREATE VIEW ([^ ]+)/CREATE VIEW OPSDM002.\1/' dim_copy.20080516.sql
sed: illegal option -- r
Usage: sed [-n] Script [File ...]
sed [-n] [-e Script] ... [-f Script_file] ... [File ...]

Thanks

6

In that case, here we go with the backslash dance:

sed '/OPSDM002/! s/CREATE VIEW \([^ ]\+\)/CREATE VIEW OPSDM002.\1/'
7

Ripat ,
This is not working too.

#sed '/OPSDM002/! s/CREATE VIEW \([^ ]\+\)/CREATE VIEW OPSDM002.\1/' dim_copy.20080516.sql
sed: /OPSDM002/! s/CREATE VIEW \([^ ]\+\)/CREATE VIEW OPSDM002.\1/ is not a recognized function.

Thanks

8

Ripat ,
This is not working too.

#sed '/OPSDM002/! s/CREATE VIEW \([^ ]\+\)/CREATE VIEW OPSDM002.\1/' dim_copy.20080516.sql
sed: /OPSDM002/! s/CREATE VIEW \([^ ]\+\)/CREATE VIEW OPSDM002.\1/ is not a recognized function.

Thanks

9

What sed version are you working with? It works fine on my GNU sed 4.1.5 and I don't have any other version to test on. Maybe somebody else?

10

how do you see sed version ?

I tried sed -V but it doesn't work .

Any input ....

Thanks

11

Bah! Use 'awk':

awk '/^CREATE VIEW/ && !/OPSDM02/{$3="OPSDM02."$3};{print}' infile > outfile

It gets a bit more compliated if the string "CREATE VIEW" really needs to be case-insensitive. You'd need to make the regex [Ll][Ii][Kk][Ee] [Tt][Hh][Ii][Ss]. If the string is either ALL upper or ALL lower, then you can get away with using "/^CREATE VIEW/ || /^create view/".

12

The sed expression above is missing a forward-slash.

Or wait, no it's not. That DOES look right! Weird, nevermind. Maybe this version of sed uses "$1" instead of "\1"??

13

Thanks a lot Gus, Its working now.
I am using translate to upper case all the words in sql file so grep for " CREATE VIEW " is ok .

Thanks to others for their time too !

14

Thanks a lot Gus, Its working now.
I am using translate to upper case all the words in sql file so grep for " CREATE VIEW " is ok .

Thanks to others for their time too !