Showing posts with label examples. Show all posts
Showing posts with label examples. Show all posts

Nov 25, 2012

Unix Grep Examples


Grep
######

1) How to exclude lines in a grep

syntax: grep -v <pattern> <filename>

e.g.,
>> cat test.txt
prabhath
ramesh
vamsi
lakshmi muvvala
PRABHATH
LAKSHMI MUVVALA

>> grep -v "prabhath" test.txt

It will exclude the lines which contain "prabhath"

o/p:
ramesh
vamsi
lakshmi muvvala
PRABHATH
LAKSHMI MUVVALA


2) How to exclude lines in a grep with ignore case

syntax: grep -iv <pattern> <filename>

e.g.,
>> cat test.txt
prabhath
ramesh
vamsi
lakshmi muvvala
PRABHATH
LAKSHMI MUVVALA

>> grep -iv "prabhath" test.txt

It will exclude the lines which contain "prabhath", "Prabhath", "PRABAHTAH" etc.,

o/p:
ramesh
vamsi
lakshmi muvvala
LAKSHMI MUVVALA

3) How to exclude lines which contain multiple patterns at one shot

Syntax: grep -v -e <pattern> -e <pattern> <filename>

e.g.,
>> cat test.txt
prabhath
ramesh
vamsi
lakshmi muvvala
PRABHATH
LAKSHMI MUVVALA


>>grep -v -e "prabhath" -e "lakshmi" test.txt

It will exclude the lines which contains the pattern "prabhath" & pattern "lakshmi"

o/p:
ramesh
vamsi
PRABHATH
LAKSHMI MUVVALA


>>grep -iv -e "prabhath" -e "lakshmi" test.txt

Please note "i" - ignore case
It will exclude the lines which contains the pattern "prabhath", "PRABHATH" & pattern "lakshmi", "LAKSHMI"

o/p:
ramesh
vamsi


4) Counting the number of matches using grep -c

Syntax: grep -c "pattern" filename

e.g.,
>> cat test.txt
prabhath
ramesh
vamsi
lakshmi muvvala
PRABHATH
LAKSHMI MUVVALA

>>grep -c "prabhath" test.txt
1

>>grep -ic "prabhath" test.txt
2


5) Counting the number of lines that does not match the pattern

e.g.,
>> cat test.txt
prabhath
ramesh
vamsi
lakshmi muvvala
PRABHATH
LAKSHMI MUVVALA

>>grep -vc prabhath test.txt
5

(or)

>>grep -v -c prabhath test.txt
5


6) Display N lines around match (Before & After)

Syntax: > grep -C 2 "Pattern" File

e.g.,
>> cat test.txt
prabhath
ramesh
vamsi
lakshmi muvvala
PRABHATH
LAKSHMI MUVVALA


>> grep -C 2 "lakshmi" test.txt

o/p:
ramesh
vamsi
lakshmi muvvala //Actual Match
PRABHATH
LAKSHMI MUVVALA


7) Display N lines Before Match

Syntax: > grep -B 2 "Pattern" File

e.g.,
>> cat test.txt
prabhath
ramesh
vamsi
lakshmi muvvala
PRABHATH
LAKSHMI MUVVALA

>> grep -B 2 "lakshmi" test.txt

o/p:
ramesh
vamsi
lakshmi muvvala //Actual Match


8) Display N lines After Match

Syntax: >> grep -A 2 "Pattern" File

e.g.,
>> cat test.txt
prabhath
ramesh
vamsi
lakshmi muvvala
PRABHATH
LAKSHMI MUVVALA

>> grep -A 2 "lakshmi" test.txt

o/p:
lakshmi muvvala //Actaul Match
PRABHATH
LAKSHMI MUVVALA


9)  Checking for full words, not for sub-strings using grep -w

e.g.,
>> cat test.txt
prabhath
prabhathkota
prabhath kota
ramesh
vamsi
lakshmi muvvala
PRABHATH
PRABHATHKOTA
PRABHATH KOTA
LAKSHMI MUVVALA

Syntax: >> grep -w "Pattern" File

>>grep -iw "kota" test.txt

o/p:
prabhath kota
PRABHATH KOTA


>>grep -i "kota" test.txt

o/p:
prabhathkota
prabhath kota
PRABHATHKOTA
PRABHATH KOTA


10) How to get line numbers in grep

>> cat test.txt
prabhath
prabhathkota
prabhath kota
ramesh
vamsi
lakshmi muvvala
PRABHATH
PRABHATHKOTA
PRABHATH KOTA
LAKSHMI MUVVALA


>> grep -n 'PRABHATH' test.txt

o/p:
7 PRABHATH
8 PRABHATHKOTA
9 PRABHATH KOTA

>> grep -in 'PRABHATH' test.txt

o/p:
1 prabhath
2 prabhathkota
3 prabhath kota
7 PRABHATH
8 PRABHATHKOTA
9 PRABHATH KOTA


11) Grep Pipe Examples

1) Display cpu model name

#With Pipe
]# cat /proc/cpuinfo | grep -i 'Model'

#Without Pipe
]# grep -i 'Model' /proc/cpuinfo    

2) grep dbname *.dbl | grep mysql


12) Some complex examples using Grep...

>> cat test.txt
prabhath
prabhathkota
prabhath kota
ramesh
vamsi
lakshmi muvvala
PRABHATH
PRABHATHKOTA
PRABHATH KOTA
LAKSHMI MUVVALA


>> grep -nC 2 'lakshmi' test.txt

Note: "-C 2" will grep for 2 lines above and 2 lines below
          "-n" will give the line numbers

o/p:
4 ramesh
5 vamsi
6 lakshmi muvvala
7 PRABHATH
8 PRABHATHKOTA


13) Grep OR

1) grep 'pattern1\|pattern2' filename

$ cat employee.txt
100  Thomas  Manager    Sales       $5,000
200  Jason   Developer  Technology  $5,500
300  Raj     Sysadmin   Technology  $7,000
400  Nisha   Manager    Marketing   $9,500
500  Randy   Manager    Sales       $6,000


$ grep 'Tech\|Sales' employee.txt
100  Thomas  Manager    Sales       $5,000
200  Jason   Developer  Technology  $5,500
300  Raj     Sysadmin   Technology  $7,000
500  Randy   Manager    Sales       $6,000

2) grep -E 'pattern1|pattern2' filename

$ grep -E 'Tech|Sales' employee.txt
100  Thomas  Manager    Sales       $5,000
200  Jason   Developer  Technology  $5,500
300  Raj     Sysadmin   Technology  $7,000
500  Randy   Manager    Sales       $6,000


3) egrep 'pattern1|pattern2' filename

$ egrep 'Tech|Sales' employee.txt
100  Thomas  Manager    Sales       $5,000
200  Jason   Developer  Technology  $5,500
300  Raj     Sysadmin   Technology  $7,000
500  Randy   Manager    Sales       $6,000


14) Grep AND

grep -E 'pattern1.*pattern2' filename
grep -E 'pattern1.*pattern2|pattern2.*pattern1' filename

$ grep -E 'Dev.*Tech' employee.txt
200  Jason   Developer  Technology  $5,500

The following example will grep all the lines that contain both “Manager” and “Sales” in it (in any order).
$ grep -E 'Manager.*Sales|Sales.*Manager' employee.txt


15) Grep NOT

grep -v 'pattern1' filename

$ grep -v Sales employee.txt
200  Jason   Developer  Technology  $5,500
300  Raj     Sysadmin   Technology  $7,000
400  Nisha   Manager    Marketing   $9,500

$ egrep 'Manager|Developer' employee.txt | grep -v Sales
200  Jason   Developer  Technology  $5,500
400  Nisha   Manager    Marketing   $9,500


16) Highlighting the search using GREP_OPTIONS

$ export GREP_OPTIONS='--color=auto' GREP_COLOR='100;8'

$ grep this demo_file
this line is the 1st lower case line in this file.
Two lines above this line is empty.
And this is the last line.



17) Show only the matched string

#] grep -o  

$ grep -o "is.*line" demo_file

is line is the 1st lower case line
is line
is is the last line

Sep 13, 2012

MySQL/Oracle Database Examples

How to find Difference b/w dates
####################################
select WSM_RMS_MSISDN_TYPE, COUNT(*) from WSM_REMINDER_SERVICE_LOG where
WSM_RMS_SEND_DT >= to_date('30-11-2008 00:00:00','DD-MM-YYYY HH24:MI:SS') and
WSM_RMS_SEND_DT <= to_date('02-12-2008 23:59:59','DD-MM-YYYY HH24:MI:SS') group by WSM_RMS_MSISDN_TYPE;

SQL> create table datemath (start_date date,end_date date);
SQL> insert into datemath (start_date,end_date)
values ( to_date('01-OCT-2006 13:00:00', 'DD-MON-YYYY HH24:MI:SS'), to_date('01-OCT-2006 14:00:00','DD-MON-YYYY HH24:MI:SS') );
SQL> commit;

SQL> select to_char(start_date,'DD-MON-YYYY HH24:MI:SS') START_DATE ,to_char(end_date,'DD-MON-YYYY HH24:MI:SS') END_DATE
     from datemath;
START_DATE END_DATE
-------------------- --------------------
01-OCT-2006 13:00:00 01-OCT-2006 14:00:00


SQL> select to_char(start_date,'DD-MON-YYYY HH24:MI:SS') START_DATE ,to_char(end_date,'DD-MON-YYYY HH24:MI:SS') END_DATE
from datemath;

START_DATE END_DATE
-------------------- --------------------
01-OCT-2006 13:10:00 10-OCT-2006 14:00:00


****************************************************************************************************************
SQL> select to_char( start_date, 'dd-mon-yyyy hh24:mi:ss' ) start_date,
2 trunc( end_date-start_date ) days,
3 trunc( mod( (end_date-start_date)*24, 24 ) ) hours,
4 trunc( mod( (end_date-start_date)*24*60, 60 ) ) Minutes,
5 trunc( mod( (end_date-start_date)*24*60*60, 60 ) ) Seconds,
6 to_char( end_date, 'dd-mon-yyyy hh24:mi:ss' ) end_date
7* from datemath;

START_DATE           Days  HOURS  MINUTES   SECONDS END_DATE
-------------------- ----- -----  --------  ------  ----------
01-oct-2006 13:10:00 9     0      50        0       10-oct-2006 14:00:00
****************************************************************************************************************

sql> select end_date - start_date from datemath;
END_DATE - START_DATE
-------------------
.041666667

SQL> select (end_date-start_date)*24 hours from datemath;
HOURS
----------
1

Multiply by 24 and again by 60 to get the number of minutes

SQL> select (end_date-start_date)*24*60 minutes from datemath;
MINUTES
----------
60

Multiply by 24 and again by 60 and again by 60 to get the number of seconds

SQL> select (end_date-start_date)*24*60*60 seconds from datemath;
SECONDS
----------
3600


MySQL/Oracle Statements
##########################

Select * from eng_traslate where PROG_ID = '9041' and rownum > 0 and rownum <=2;

select * from (select WSM_RMS_MSISDN, COUNT(*) from WSM_REMINDER_SERVICE_LOG
                where WSM_RMS_SEND_DT >= to_date('30-11-2008 00:00:00','DD-MM-YYYY HH24:MI:SS') and
                      WSM_RMS_SEND_DT <= to_date('02-12-2008 23:59:59','DD-MM-YYYY HH24:MI:SS')
                group by WSM_RMS_MSISDN
              )
where ROWNUM >0 and ROWNUM <= 2; 


select WSM_RMS_MSISDN_TYPE as op_type, WSM_RMS_STATUS as status, COUNT(*) as CNT
from WSM_REMINDER_SERVICE_LOG
where WSM_RMS_SEND_DT >= to_date('30-11-2008 00:00:00','DD-MM-YYYY HH24:MI:SS') and
      WSM_RMS_SEND_DT <=  to_date('02-12-2008 23:59:59','DD-MM-YYYY HH24:MI:SS')
group by WSM_RMS_MSISDN_TYPE, WSM_RMS_STATUS
order by op_type;


sql> CREATE TABLE DATETEST(DATECOL DATE);
sql> INSERT INTO DATETEST(DATECOL) VALUES (TO_DATE('01-JAN-2006 08:18:23','DD-MON-YYYY HH24:MI:SS'));
sql> commit;

- The default dispaly of DATE column is 'DD-MON-YY'
- U can change the format of the default dispaly of DATE easily.

SQL> select datecol from datetest;
DATECOL
---------
01-JAN-06

SQL> alter session set nls_date_format='DD/MON/YYYY';
Session altered.

SQL> select datecol from datetest;
DATECOL
-----------
01/JAN/2006



SQL> create or replace view text2date as select to_date('05-JAN-2006','DD-MON-YY') text2date111 from dual;
View created.

SQL> desc text2date
Name Null? Type
-----------------
TEXT2DATE111 DATE


SQL> select text2date111 from text2date;
TEXT2DATE111
------------
05-JAN-06

SQL> select to_char(text2date,'Day') text2date from text2date;
TEXT2DATE111
-------------
Thursday


Enum:
#######
CREATE TABLE employee_person (
    id int unsigned not null primary key,
    address varchar(60),
    phone int,
    email varchar(60),
    birthday DATE,
    sex ENUM('M', 'F'),
    m_status ENUM('Y','N'),
    s_name varchar(40),
    children int
);

Grant Previliges
#####################

create database atmail123;                                              

mysql> grant all on atmail123.* to root@112.118.61.125 identified by 'mp0d';
mysql> grant all on atmail123.* to webmail@112.119.600.115 identified by 'webmail';
mysql> grant all on atmail123.* to root@114.135.61.102 identified by 'mp0d';