Sunday, August 30, 2009

Find and Replace using grep and sed

Following command finds all the occurences of <br> in all .xml files in the current directory, and replace those <br> with <br/> . Please note that < and > have been surrounded with [ and ] in the sed command. / in the <br/> tag has been escaped using \. If you want to replace foo with bar, then you can just use those words.
find . -iname "*.xml" | xargs grep -l "<br>" | xargs sed -i -e 's/[<]br[>]/<br\/>/g'
find . -iname "*.xml" | xargs grep -l "<br>" | xargs sed -i -e 's/foo/bar/g'
A more general form of the expression would be
find . \( -name "*.php" -or -name "*.html" \) | xargs grep -l 'TEXT TO SEARCH' | xargs sed -i -e 's/TEXT TO REPLACE/REPLACEMENT STRING/g'

Thursday, August 27, 2009

Rename Files with find command in linux

If you want to rename files recusivelly using linux find command, here are two example usage of it with exec and xargs. This command will convert all the *.xml files into *.html files.

Using xags
find . | xargs rename 's/\.xml$/\.html/i'
Using exec
find . -exec rename 's/\.xml$/\.html/i' {} +
Above commands recurrsively replace files within inner directories too. If you want to do it for current directory only you can use -prune.
find . -prune | xargs rename 's/\.xml$/\.html/i'
find . -prune -exec rename 's/\.xml$/\.html/i' {} +

Monday, August 10, 2009

Multiple Joins on the same table

Here are few example queries which shows how to use multiple inner joins with sql.

SELECT Fruit1, F1FruitName = F1.FruitName, F1FruitCost = F1.FruitCost,
Fruit2, F2FruitName = F2.FruitName, F2FruitCost = F2.FruitCost
JOIN T2 F1 ON Fruit1 = F1.Fruit_ID
JOIN T2 F2 ON Fruit2 = F2.Fruit_ID
If you want each fruit on it's on line(Record set), then it looks like
SELECT UserID, Fruit1 AS FruitID, FruitName, FruitCost
JOIN T2 ON Fruit1 = Fruit_ID
SELECT UserID, Fruit2 AS FruitID, FruitName, FruitCost
JOIN T2 ON Fruit2 = Fruit_ID
Here is two equivalent queries where one of them uses join and other doesn't.
SELECT codice, 
other1.value AS value_one, 
other2.value AS value_two
FROM main
LEFT OUTER JOIN other as other1 ON ( = other1.id_main AND type = 'type_one')
LEFT OUTER JOIN other as other2 ON ( = other2.id_main AND type = 'type_two)
is equivalent to
SELECT codice,
(SELECT value FROM other WHERE id_main = id AND type = 'type_one') AS value_one,
(SELECT value FROM other WHERE id_main = id AND type = 'type_two') AS value_two
FROM codice
Query below will give you a clear idea about how to use mutliple joins in the same query. Hope this will help you to understand the usage.
SELECT u1.displayname AS Player1
, u2.displayname AS Player2
, u3.displayname AS Player3
, u4.displayname AS Player4
, u5.displayname AS Player5
, u6.displayname AS Player6
DCMLeagues AS L
JOIN Users AS u1 
ON u1.userid = L.player1
JOIN Users AS u2
ON u2.userid = L.player2
JOIN Users AS u3 
ON u3.userid = L.player3
JOIN Users AS u4 
ON u4.userid = L.player4
JOIN Users AS u5 
ON u5.userid = L.player5
JOIN Users as u6 
ON u6.userid = L.player6