CODEWITHSUNDEEP
sql-serverregexbashsed
activelearner
activelearner

Reputation: 7745

Sed - Replace all occurrences of a string in the second last line of a file

I have a bash script that I am using to modify a sql file (test.sql).

The sql file is as follows:

select count(*) from (
select 
I.ID,
I.START_DATE, 
I.END_DATE
from MY_TABLE I with (nolock)
where I.START_DATE >= '20170101' and I.START_DATE < '20170201'
) as cnt

I want to replace all the occurrences of the string START_DATE but only in the WHERE clause of the sql file.

So far, I have tried this:

#!/bin/bash
sed 'x; ${/START_DATE/s/START/END;p;x}; 1d' test.sql > new-test.sql

However, this returns the following new-test.sql file:

select count(*) from (
select 
I.ID,
I.START_DATE, 
I.END_DATE
from MY_TABLE I with (nolock)
where I.END_DATE >= '20170101' and I.START_DATE < '20170201'
) as cnt

The second occurrence of the string START_DATE in the WHERE clause is not being replaced.

How should I modify my sed expression so that I can achieve this?

Upvotes: 0

Views: 2171

Answers (2)

user4918296
user4918296

Reputation:

If you are using GNU sed then you can use the following command

sed '/WHERE/I s/START_DATE/END_DATE/g' test.sql > new-test.sql
            ^                        
            |-------------------------- case insensitive match; GNU sed only

The capital I tells sed to perform a case insensitive match. This comes in handy when dealing with case insensitive SQL commands.

If you do not have GNU sed then case insensitive matching is a bit more complicated:

sed '/[wW][hH][eE][rR][eE]/ s/START_DATE/END_DATE/g' test.sql > new-test.sql

Simply adding the global g option at the end of the s command will make your solution work, too.

sed 'x; ${/START_DATE/s/START/END/g;p;x}; 1d'

However, it will break if the file has a trailing newline. I strongly recommend to search (case insensitively) for the WHERE clause and run the according substitution command.

Upvotes: 0

randomir
randomir

Reputation: 18697

Try it like this:

sed -e '/where/ s/START_DATE/END_DATE/g' -i test.sql

Explanation:

  • operate only on lines that contain where (we "address" only the lines that match the regex pattern where)

  • replace each occurrence of START_DATE with END_DATE - notice the "global" flag g at the end

  • the -i flag tells sed to edit the file "in place" (no need to redirect output).

Upvotes: 1

Related Questions