Sunday, 19 November 2017

sed command usage and examples

SED is a streamline editor screen-oriented utility. The sed stream editor is a text editor that performs editing operations on information coming from standard input or a file. The stream editor is ideally suited to performing repetitive edits that would take considerable time if done manually. Sed edits line by line; it reads the next line of the file and repeats the process until it is finished with the file.
Syntax
sed options 'command' filename

Let us use below test.txt file for our sed examples.

This is a test file to learn sed command.
SED is a streamline editor.
We will learn usage of sed command with various examples.
This is a test file to learn sed command.
The sed stream editor is a text editor that performs editing operations on information coming from standard input or a file. 
Sed edits line by line.
This is a test file to learn sed command.

Print mth line from the file.

Syntax
sed -n np filename

Example
sed -n 2p test.txt

Output
SED is a streamline editor.

Print certain range (n1 to n2) of lines from the file.

Syntax
sed -n 'n1,n2p' filename

Example
sed -n '2,4p' test.txt

Output
SED is a streamline editor.
We will learn usage of sed command with various examples.
This is a test file to learn sed command.

Print every nth line starting with the specific mth line

Syntax
sed -n 'm~np' filename

testnew.txt file
This is a test file to learn sed command.
SED is a streamline editor.
We will learn usage of sed command with various examples.
This is a test file to learn sed command.
The sed stream editor is a text editor that performs editing operations on information coming from standard input or a file. 
Sed edits line by line.
This is a test file to learn sed command.
This line is added to understand current example.

Example
sed -n '2~3p' testnew.txt

Output
SED is a streamline editor.  
The sed stream editor is a text editor that performs editing operations on information coming from standard input or a file. 
This line is added to understand current example.

Print only specific lines from file.
We need to use execute option for this purpose as shown below to display 2nd and 5th line.

Syntax
sed -n -e 2p -e 5p test.txt

Output
SED is a streamline editor.
The sed stream editor is a text editor that performs editing operations on information coming from standard input or a file. 

Print line from the file containing specific pattern.

Syntax
sed -n '/pattern/p' filename

Example
sed -n '/edit/p' test.txt

Output
SED is a streamline editor.
The sed stream editor is a text editor that performs editing operations on information coming from standard input or a file. 
Sed edits line by line.

Print line from the file starting with number.
sed -n '/^[0-9]/p' filename

Print line from the file containing consecutive 4 numbers.
sed '/[0-9]\{4\}/p' file.txt

Print line from the file with specific string in uppercase.

Syntax
sed -n 's/lowercasestring/\U&p' filename

Example
sed -n 's/test/\U&p' test.txt

Output
This is a TEST file to learn sed command.
This is a TEST file to learn sed command.
This is a TEST file to learn sed command.

Use L instead of U for lowercase.

Display particular paragraph from the file

Syntax
sed -n '/startofpara/,/endofpara/p' filename

testfile.txt
This is a test file race
You are so fantastic barry.
Run barry Run.

All the great things happens from small begining.

Super dancer go to dance floor.
Have a rocking performance.

Example
sed -n '/This/,/Run/p' testfile

Output
This is a test file race
You are so fantastic barry.
Run barry Run.

Print lines from particular line number to specific string from the file

Syntax
sed -n 'num,/string/p' filename

Example
sed -n '1,/fantastic/p' testfile

Output
This is a test file race
You are so fantastic barry.

Display the file excluding nth line from the file.

Syntax
sed nd filename

Example
sed 2d test.txt

Output
This is a test file to learn sed command.
We will learn usage of sed command with various examples.
This is a test file to learn sed command.
The sed stream editor is a text editor that performs editing operations on information coming from standard input or a file. 
Sed edits line by line.
This is a test file to learn sed command.

Display the file excluding last line from the file.

Syntax
sed '$d' filename

Example
sed '$d' test.txt

Output
This is a test file to learn sed command.
SED is a streamline editor.
We will learn usage of sed command with  various examples.
This is a test file to learn sed command.
The sed stream editor is a text editor that performs editing operations on information coming from standard input or a file. 
Sed edits line by line.

Display the file excluding certain range(n1 to n2) of lines.

Syntax
sed 'n1,n2d' filename

Example
sed '2,4d' test.txt

Output
This is a test file to learn sed command.
The sed stream editor is a text editor that performs editing operations on information coming from standard input or a file. 
Sed edits line by line.
This is a test file to learn sed command.

Replacing a string with another string

Syntax
sed s/stringtoreplace/newstring/ filename

Example
sed s/test/dummy/ test.txt

Output
This is a dummy file to learn sed command.
SED is a streamline editor.
We will learn usage of sed command with  various examples.
This is a dummy file to learn sed command.
The sed stream editor is a text editor that performs editing operations on information coming from standard input or a file. 
Sed edits line by line.
This is a dummy file to learn sed command.

Replacing multiple strings with another string in the entire file at the same time

Syntax
sed 's/stringtoreplace/newstring/;s/stringtoreplace/newstring/' file

Example
sed 's/test/dummy/;s/usage/operations/' test.txt

Output
This is a dummy file to learn sed command.
SED is a streamline editor.
We will learn operations of sed command with various examples.
This is a dummy file to learn sed command.
The sed stream editor is a text editor that performs editing operations on information coming from standard input or a file. 
Sed edits line by line.
This is a dummy file to learn sed command.

Replacing a string with other string in the file for specific line

Syntax
sed 'n s/existingstring/newstring/' filename

Example
sed '4 s/test/dummy/' test.txt

Output
This is a test file to learn sed command.
SED is a streamline editor.
We will learn usage of sed command with  various examples.
This is a dummy file to learn sed command.
The sed stream editor is a text editor that performs editing operations on information coming from standard input or a file. 
Sed edits line by line.
This is a test file to learn sed command.

Replacing a string with other string in the file except specific line

Syntax
sed '!n s/existingstring/newstring/' filename

Example
sed '!4 s/test/dummy/' test.txt

Output
This is a dummy file to learn sed command.
SED is a streamline editor.
We will learn operations of sed command with  various examples.
This is a test file to learn sed command.
The sed stream editor is a text editor that performs editing operations on information coming from standard input or a file. 
Sed edits line by line.
This is a dummy file to learn sed command.

We can also use range instead of number if required.
sed 'n1,n2 s/existingstring/newstring/' filename

Replacing a string with another string in the file for the lines if specific string found

Syntax
sed '/specificstring s/stringtoreplace/newstring/' filename

Replacing a string with another string in the file for all the lines except specific string found.

Syntax
sed '/specificstring/!s/stringtoreplace/newstring/' filename

Example
sed '/sed s/usage/operation/' filename

Output
This is a test file to learn sed command.
SED is a streamline editor.
We will learn operation of sed command with  various examples.
This is a test file to learn sed command.
The sed stream editor is a text editor that performs editing operations on information coming from standard input or a file. 
Sed edits line by line.
This is a test file to learn sed command.

Inserting BLANK lines in file

Syntax
sed G filename

Example
sed G test.txt

Output

This is a test file to learn sed command.

SED is a streamline editor.

We will learn usage of sed command with various examples.

This is a test file to learn sed command.

The sed stream editor is a text editor that performs editing operations on information coming from standard input or a file. 

Sed edits line by line.

This is a test file to learn sed command.


To insert two blank lines execute following

Syntax
sed 'G:G' filename

In place editing of the file and performing sed operations on nth occurrence of matched string in the line

Syntax
sed -i 's/replacestring/newstring/n' test.txt

Below example will modify the test.txt file.

sed -i 's/test/dummy/gi' test.txt

g - global replace i.e. replace every instance of occurence of string in the line.
i - ignore case

Below example will modify the test.txt file and also keep the original test.txt file with org extension.

sed -i'.org' 's/test/dummy/gi' test.txt

Executing sed commands through script

Syntax
sed -f script.sed filename

cat script.sed
s/test/dummy

Example
sed -f script.sed test.txt

Output
This is a dummy file to learn sed command.
SED is a streamline editor.
We will learn usage of sed command with various examples.
This is a dummy file to learn sed command.
The sed stream editor is a text editor that performs editing operations on information coming from standard input or a file. 
Sed edits line by line.
This is a dummy file to learn sed command.

Alternative for head command

Syntax
sed nq filename

Example
sed 3q test.txt

Output
This is a test file to learn sed command.
SED is a streamline editor.
We will learn usage of sed command with various examples.

Print last line of the file also Alternative for tail-1 command.

Syntax
sed -n '$p' test.txt

Output
This is a test file to learn sed command.

Sunday, 1 October 2017

SELECT and SELECT DISTINCT SQL QUERY

SELECT STATEMENT
SELECT identifies what columns to fetch and FROM identifies which table to refer. The SQL SELECT statement returns a result set of records from one or more tables. A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used data query language (DQL) command. As SQL is a declarative programming language, SELECT queries specify a result set, but do not specify how to calculate it. The database translates the query into a "query plan" which may vary between executions, database versions and database software. This functionality is called the "query optimizer" as it is responsible for finding the best possible execution plan for the query, within applicable constraints.

The SELECT statement has many optional clauses:

WHERE specifies which rows to retrieve.

GROUP BY groups rows sharing a property so that an aggregate function can be applied to each group.

HAVING selects among the groups defined by the GROUP BY clause.

ORDER BY specifies an order in which to return the rows.

AS keyword provides an alias which can be used to temporarily rename tables or columns.

Syntax

SELECT *| {[DISTINCT] COLUMN_NAME|EXPRESSION [ALIAS],...}
FROM TABLE_NAME;


SELECT
Is a list of one or more columns
*
Selects all columns
DISTINCT
Suppresses duplicates
column|expression
Selects the named column or the expression
alias
Gives selected columns different headings
FROM table
specifies the table to refer containing the columns

Example
Below SELECT example will show all the rows from students table.


SELECT * FROM STUDENTS;









DISTINCT STATEMENT
A column may contain several duplicate values; and generally you merely wish to list the various (distinct) values. The SELECT DISTINCT clause is used with SELECT statement to eliminate all the duplicate records and show only distinctive records. In SQL, the DISTINCT clause doesn't ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value. DISTINCT can be used with aggregates function and operates only on a single column.

Syntax

SELECT DISTINCT COLUMN_NAME1, COLUMN_NAME2, ...
FROM TABLE_NAME;

Example
DISTINCT clause is used to find the unique values within one column in a table.

Below example shows 6 city from students table. There is a duplicate entry of central city.


SELECT CITY FROM STUDENTS;










By using DISTINCT clause we can eliminate this duplicity and fetch only unique city names.


SELECT DISTINCT CITY FROM STUDENTS;