SED - Unix stream editor can be used to manipulate text files

Why SED? Examples

Use case: Parametrising a report

One example where SED can be useful is when we create a report with parameters but first test the query replacing the parameters by actual values.

After we have tested the query we will want to replace the values by some parameters. Now imagine, you have a long query with many parameters so that replacing all of them even using the 'replace all' button of the text editor becomes quite time consuming. And what if we have many similar queries (e. g. sub-reports) with the same parameters?

A quick way to replace strings in multiple files is to use the sed function from the terminal of your Unix / Linux system.

See the section 'How to use' for the details.

Use case: Redefining field format

Another example: we have a report with 10 sub-reports where all number fields have a format of

"$#,##0"

1. From the command line cd to the directory where the .report files are located
2. type in

   sed -i 's/$#,##0./#,##0./g'  *.report

Use case: Convert a file (input.txt) to all lower case (output.txt) or vice versa

upper to lower:

$ sed -e 's/\(.*\)/\L\1/' input.txt > output.txt

lower to upper:

$ sed -e 's/\(.*\)/\U\1/' input.txt > output.txt

How to use

To replace the string 'old' by the string 'new' in all files in the specific folder that end with .sql, cd to the location of the files and use:

sed -i 's/old/new/g'  *.sql

By the way, if you first want to test which files will be changed by this command, use 'grep' to search for the string that you are going to replace

grep 'old' *.sql

You can also write this code into a .shell file and execute the file.

For example, if you need to replace three values (val1, val2, val3) by the parameters (par1, par2, par3) in all .sql files which are in the directory dir, write the following code in a text editor

sed -i 's/{par1}/val1/g'  *.sql
sed -i 's/{par2}/val2/g'  *.sql
sed -i 's/{par3}/val3/g'  *.sql

then

1. save the file in the directory 'dir' naming it something like 'replace_parameters.sh'.
2. cd to the directory 'dir' and execute the file

sh replace_parameters.sh

Useful Resources

• wikipedia
• An Introduction and Tutorial