Requirement:
Suppose we have column named Name1 (consisting of first name and last name) with following data:
Name1
-------------
Arpu Anau
Stev Smith
I want to match first and last characters of first and last names. If they are same show that row or show TRUE for that row.
Means
Arpit Anau is having A=A and u=u. So, TRUE
Stev Smith is having S=S but v!=h. So, FALSE
Example without Regular Exprssion:
WITH t AS
(
SELECT 'Arpu Anau' Name1 FROM DUAL UNION ALL
SELECT 'Stev Smith' Name1 FROM DUAL
)
SELECT FN, LN, CASE WHEN SUBSTR(FN,1,1) = SUBSTR(LN,1,1) AND SUBSTR(FN,-1,1) = SUBSTR(LN,-1,1) THEN 'TRUE' ELSE 'FALSE' END
FROM (SELECT SUBSTR(Name1,1,INSTR(Name1,' ')-1) FN, SUBSTR(Name1,INSTR(Name1,' ')+1) LN
FROM t)
SQL> /
FN LN CASEW
---------- ---------- -----
Arpu Anau TRUE
Stev Smith FALSE
With Regular Expresion [example uses backreferences in Regular Expression]
SQL> WITH t AS
(
SELECT 'Antu Anamentu' Name1 FROM DUAL UNION ALL
SELECT 'Steven Smith' Name1 FROM DUAL
)
SELECT Name1
FROM t
WHERE REGEXP_LIKE (Name1, '^(.).*(.) \1.*\2$') ;
NAME1
-------------
Antu Anamentu
Explanation:
^(.) =>^ specifies the next char is first char
. dot specifies single character
() defines group which can be backrferenced
.* => Any number of characters as we want only first and last character
(.) => (.)space The space signifies that we want to remember last char of first word or char before space
\1 => It recalls first group which we saved for backreference using (.)
.* => Any number of chars
\2 => It recalls first group which we saved for backreference using (.)
$ => It specifies the char before this is last char
Thursday, May 29, 2008
Wednesday, May 28, 2008
My Trip to Niagara Fall
As 24, 25, 26-May-08 (Memorial Day holiday) was long weekend, we decided to visit Niagara Fall. We includes: Nilang, Keval, Kunal, Hardeep, Pravin, Naveen, Sanjay and myself.
Planning:
Earlier we were planning to rent a Car and drive it. But then where were few hurdles like the rented Van can accomodate only 7 people. And we were 8. And since all were not driving we were not able to rent two cars. Which seemed to be lucrative option as cheaper and comfortable compare to 7 ppl in one van. Finally we decided to go by bus. Kunal booked our ticket on gotobus.com . I bought Haldiram Namkin packets for trip.
Trip Day:
Reached Bus Stop at Top Quality Food Plaza [828 Rt 46 W]
As the route of bus was via Parsippany, all decided to meet at my house and then go to bus stop. For, Kunal New York was near and easier to commute, so he went to New York. Pravin came to my house previous night. On Saturday morning at 8 o'clock, Nilang, Keval, Sanjay, Praving, Hardeep came to my house. I drove three frnds to bus stop and parked my Car at sister's house. My room partner Nirav dropped remaining 4 to bus stop. He also picked me up from my sister's house.
Bus Journey:
We started at 9:15am by bus. Actually bus was waiting for us only. As soon as we get into bus, it started. It was 2X2 coach with 58 seats. Ours were last 8 seats. Bus was full of desis. Out of 58, almost 48 were desis. And bus was of Chinese Travel Agency called "Happy Vacations". Seems desis need to venture into travels ;-)
Guide was chinese guy named Leo. We took first break at almost 12:15pm. Bus stopped at Burger King(BK). It looked like small BK was filled with tourist of 4 buses. I forgot to mention but there were almost 5 buses that were running parallely on same route. There were queue everywhere for food, for restrooms :) So we went to Subway, at distance of 5 mins walk.
Remaining will write later.
Planning:
Earlier we were planning to rent a Car and drive it. But then where were few hurdles like the rented Van can accomodate only 7 people. And we were 8. And since all were not driving we were not able to rent two cars. Which seemed to be lucrative option as cheaper and comfortable compare to 7 ppl in one van. Finally we decided to go by bus. Kunal booked our ticket on gotobus.com . I bought Haldiram Namkin packets for trip.
Trip Day:
Reached Bus Stop at Top Quality Food Plaza [828 Rt 46 W]
As the route of bus was via Parsippany, all decided to meet at my house and then go to bus stop. For, Kunal New York was near and easier to commute, so he went to New York. Pravin came to my house previous night. On Saturday morning at 8 o'clock, Nilang, Keval, Sanjay, Praving, Hardeep came to my house. I drove three frnds to bus stop and parked my Car at sister's house. My room partner Nirav dropped remaining 4 to bus stop. He also picked me up from my sister's house.
Bus Journey:
We started at 9:15am by bus. Actually bus was waiting for us only. As soon as we get into bus, it started. It was 2X2 coach with 58 seats. Ours were last 8 seats. Bus was full of desis. Out of 58, almost 48 were desis. And bus was of Chinese Travel Agency called "Happy Vacations". Seems desis need to venture into travels ;-)
Guide was chinese guy named Leo. We took first break at almost 12:15pm. Bus stopped at Burger King(BK). It looked like small BK was filled with tourist of 4 buses. I forgot to mention but there were almost 5 buses that were running parallely on same route. There were queue everywhere for food, for restrooms :) So we went to Subway, at distance of 5 mins walk.
Remaining will write later.
Unix: sed to change characters in particular position
Suppose you have big file. You want to change character 100-110 with some text. So, simply you want to interchange first 4 characters with the next 4 characters in each line. In these kind of scenarios, sed one liners will be very useful.
See few examples below:
$cat a.tmp
20090918ARPITH2010011634
20090918ARPITH2010012050
20090905ARPITH2010011382
20090824ARPITH2010012075
20090921ARPITH2010012075
--Follwing sed command will interchange first 4 digits with next 4 digits.
$sed 's:\([0-9]\{4\}\)\([0-9]\{4\}\):\2\1:' a.tmp
09182009ARPITH2010011634
09182009ARPITH2010012050
09052009ARPITH2010011382
08242009ARPITH2010012075
09212009ARPITH2010012075
Explanation:
\([0-9]\{4\}\) It defines first four digits. If you want to replace any 4 characters not only digit then replace [0-9] with . like \(.\{4\}\)
\ It is to escape (
() This bracket will define different groups. It is used for backreference and can be used again using \1 or \2 etc based on its occurence.
[0-9] Check for digits
\{4\} Four occurence of digit in [0-9]
\2\1 It says first put second set and then first set.
Following sed command will interchange first 8 characters/digits with next 6 characters. And it will also seperate each set with space.
$sed 's:\(.\{8\}\)\(.\{6\}\):\2 \1 :' a.tmp
ARPITH 20090918 2010011634
ARPITH 20090918 2010012050
ARPITH 20090905 2010011382
ARPITH 20090824 2010012075
ARPITH 20090921 2010012075
Following command will replace characters from 9 to 15 with text "NIRAVB"
sed 's:\(.\{8\}\)\(.\{6\}\):\1NIRAVB:' a.tmp
20090918NIRAVB2010011634
20090918NIRAVB2010012050
20090905NIRAVB2010011382
20090824NIRAVB2010012075
20090921NIRAVB2010012075
Limitation: If you specify more than 255 in curly braces "\{ \}", you will get following error.
$sed 's:\(.\{256\}\)\(.\{6\}\):\1NIRAVB:' a.tmp
sed: Function s:\(.\{256\}\)\(.\{6\}\):\1NIRAVB: cannot be parsed.
Solution: To overcome the issue you can break 335 in 255 + 80. As I have shown in following example. Here I want to change character 335 to 338 with text "TEST"
$cat a.tmp
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000005
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000005
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000005
$sed 's:\(.\{335\}\)\(.\{4\}\):\1TEST:' a.tmp
sed: Function s:\(.\{335\}\)\(.\{4\}\):\1TEST: cannot be parsed.
$sed 's:\(.\{255\}\)\(.\{80\}\)\(.\{4\}\):\1\2TEST:' a.tmp
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000TEST00000000000000000000000000000000005
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000TEST00000000000000000000000000000000005
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000TEST00000000000000000000000000000000005
See few examples below:
$cat a.tmp
20090918ARPITH2010011634
20090918ARPITH2010012050
20090905ARPITH2010011382
20090824ARPITH2010012075
20090921ARPITH2010012075
--Follwing sed command will interchange first 4 digits with next 4 digits.
$sed 's:\([0-9]\{4\}\)\([0-9]\{4\}\):\2\1:' a.tmp
09182009ARPITH2010011634
09182009ARPITH2010012050
09052009ARPITH2010011382
08242009ARPITH2010012075
09212009ARPITH2010012075
Explanation:
\([0-9]\{4\}\) It defines first four digits. If you want to replace any 4 characters not only digit then replace [0-9] with . like \(.\{4\}\)
\ It is to escape (
() This bracket will define different groups. It is used for backreference and can be used again using \1 or \2 etc based on its occurence.
[0-9] Check for digits
\{4\} Four occurence of digit in [0-9]
\2\1 It says first put second set and then first set.
Following sed command will interchange first 8 characters/digits with next 6 characters. And it will also seperate each set with space.
$sed 's:\(.\{8\}\)\(.\{6\}\):\2 \1 :' a.tmp
ARPITH 20090918 2010011634
ARPITH 20090918 2010012050
ARPITH 20090905 2010011382
ARPITH 20090824 2010012075
ARPITH 20090921 2010012075
Following command will replace characters from 9 to 15 with text "NIRAVB"
sed 's:\(.\{8\}\)\(.\{6\}\):\1NIRAVB:' a.tmp
20090918NIRAVB2010011634
20090918NIRAVB2010012050
20090905NIRAVB2010011382
20090824NIRAVB2010012075
20090921NIRAVB2010012075
Limitation: If you specify more than 255 in curly braces "\{ \}", you will get following error.
$sed 's:\(.\{256\}\)\(.\{6\}\):\1NIRAVB:' a.tmp
sed: Function s:\(.\{256\}\)\(.\{6\}\):\1NIRAVB: cannot be parsed.
Solution: To overcome the issue you can break 335 in 255 + 80. As I have shown in following example. Here I want to change character 335 to 338 with text "TEST"
$cat a.tmp
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000005
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000005
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000005
$sed 's:\(.\{335\}\)\(.\{4\}\):\1TEST:' a.tmp
sed: Function s:\(.\{335\}\)\(.\{4\}\):\1TEST: cannot be parsed.
$sed 's:\(.\{255\}\)\(.\{80\}\)\(.\{4\}\):\1\2TEST:' a.tmp
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000TEST00000000000000000000000000000000005
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000TEST00000000000000000000000000000000005
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000TEST00000000000000000000000000000000005
Query to see partition info for Oracle Table
Following query will show the column name[s] on which partition is created.
SELECT *
FROM ALL_PART_KEY_COLUMNS
WHERE NAME =
Following query will give the maximum value for particular partitions.
SELECT HIGH_VALUE
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME =
Following query will give all the tables in dictionary related to PARTITION.
SELECT TABLE_NAME
FROM DICT
WHERE TABLE_NAME LIKE '%PART%'
SELECT *
FROM ALL_PART_KEY_COLUMNS
WHERE NAME =
Following query will give the maximum value for particular partitions.
SELECT HIGH_VALUE
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME =
Following query will give all the tables in dictionary related to PARTITION.
SELECT TABLE_NAME
FROM DICT
WHERE TABLE_NAME LIKE '%PART%'
Friday, May 2, 2008
Create virtual rows
create virtual rows:
The query will run for first level. Then it will see, still it is less than 10, so it will try for next level and so on....
SELECT * FROM (SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10) ;
Good Link which explains how it works:
SQL Snippets: Integer Series Generators - CONNECT BY LEVEL Method
Another way to create virtual rows is to select from ALL_OBJECTS
One more way to create virtual table:
WITH t AS
(
SELECT '01-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '02-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '03-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '04-MAR-2008' Dt FROM DUAL
)
SELECT * FROM t ;
Query to Calculate Business Days [Query to calculate date difference excluding Saturday/Sunday]
SELECT SUM(DECODE(TO_CHAR(TO_DATE('01-MAR-2008')+ROWNUM-1, 'DY'),'SUN',0,'SAT',0,1))
FROM all_objects
WHERE ROWNUM <= TO_DATE('20-MAR-2008') - TO_DATE('01-MAR-2008') + 1
Query to find Next Date and Previous date in table of current date
WITH t AS
(
SELECT '01-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '02-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '03-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '04-MAR-2008' Dt FROM DUAL
)
SELECT LEAD(Dt,1) OVER (ORDER BY dt) NEXT_DT, LAG(Dt,1) OVER (ORDER BY dt) PREV_DT
FROM t ;
The query will run for first level. Then it will see, still it is less than 10, so it will try for next level and so on....
SELECT * FROM (SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10) ;
Good Link which explains how it works:
SQL Snippets: Integer Series Generators - CONNECT BY LEVEL Method
Another way to create virtual rows is to select from ALL_OBJECTS
One more way to create virtual table:
WITH t AS
(
SELECT '01-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '02-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '03-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '04-MAR-2008' Dt FROM DUAL
)
SELECT * FROM t ;
Query to Calculate Business Days [Query to calculate date difference excluding Saturday/Sunday]
SELECT SUM(DECODE(TO_CHAR(TO_DATE('01-MAR-2008')+ROWNUM-1, 'DY'),'SUN',0,'SAT',0,1))
FROM all_objects
WHERE ROWNUM <= TO_DATE('20-MAR-2008') - TO_DATE('01-MAR-2008') + 1
Query to find Next Date and Previous date in table of current date
WITH t AS
(
SELECT '01-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '02-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '03-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '04-MAR-2008' Dt FROM DUAL
)
SELECT LEAD(Dt,1) OVER (ORDER BY dt) NEXT_DT, LAG(Dt,1) OVER (ORDER BY dt) PREV_DT
FROM t ;
Thursday, May 1, 2008
how to remove carriage return line feed from file in unix
#cat test_C
hi
my name is arpit
this is test for cr lf
reg
arpit
#od -c test_C
0000000 h i \n m y n a m e i s a r
0000020 p i t \n t h i s i s t e s t
0000040 f o r c r l f \n r e g \n a
0000060 r p i t \n
0000065
#od -b test_C
0000000 150 151 012 155 171 040 156 141 155 145 040 151 163 040 141 162
0000020 160 151 164 012 164 150 151 163 040 151 163 040 164 145 163 164
0000040 040 146 157 162 040 143 162 040 154 146 012 162 145 147 012 141
0000060 162 160 151 164 012
0000065
#cat test_C tr -d "\015\012" > op
#cat op
himy name is arpitthis is test for cr lfregarpit#
#
--tr command with -d option deletes char specified.
#od -b op
0000000 150 151 155 171 040 156 141 155 145 040 151 163 040 141 162 160
0000020 151 164 164 150 151 163 040 151 163 040 164 145 163 164 040 146
0000040 157 162 040 143 162 040 154 146 162 145 147 141 162 160 151 164
0000060
#od -c op
0000000 h i m y n a m e i s a r p
0000020 i t t h i s i s t e s t f
0000040 o r c r l f r e g a r p i t
0000060
#
hi
my name is arpit
this is test for cr lf
reg
arpit
#od -c test_C
0000000 h i \n m y n a m e i s a r
0000020 p i t \n t h i s i s t e s t
0000040 f o r c r l f \n r e g \n a
0000060 r p i t \n
0000065
#od -b test_C
0000000 150 151 012 155 171 040 156 141 155 145 040 151 163 040 141 162
0000020 160 151 164 012 164 150 151 163 040 151 163 040 164 145 163 164
0000040 040 146 157 162 040 143 162 040 154 146 012 162 145 147 012 141
0000060 162 160 151 164 012
0000065
#cat test_C tr -d "\015\012" > op
#cat op
himy name is arpitthis is test for cr lfregarpit#
#
--tr command with -d option deletes char specified.
#od -b op
0000000 150 151 155 171 040 156 141 155 145 040 151 163 040 141 162 160
0000020 151 164 164 150 151 163 040 151 163 040 164 145 163 164 040 146
0000040 157 162 040 143 162 040 154 146 162 145 147 141 162 160 151 164
0000060
#od -c op
0000000 h i m y n a m e i s a r p
0000020 i t t h i s i s t e s t f
0000040 o r c r l f r e g a r p i t
0000060
#
Subscribe to:
Posts (Atom)
