Using translate function on Unix in a shell script


echo "TRansLate" | tr [:lower:] [:upper:]

echo "Ok to continue? y or n"
read respons
if [ `echo ${respons} | tr [:lower:] [:upper:]` = "Y" ] ||
[ `echo ${respons} | tr [:lower:] [:upper:]` = "YES" ] ; then
echo "Processing..."
else
echo "Exiting"
exit 1
fi


On executing the script

/home/ruchi> ./test
TRANSLATE
Ok to continue? y or n
yes
Processing...

DELETE

delete till end of file
dG

some other delete commands

added:
:g/^$/d
delete all (global) lines that have nothing from start (^) to end ($) i.e. blank or empty lines

:.,$d
delete lines from current line (.) to end of file ($)
dG will do the same. It's not an editor command so should be done directly on the line.

between marks
:'p,'qd
delete lines starting from mark p to mark q, including both p and q

delete current line and lines below
4dd
delete current line and 3 below it

delete current line and lines above it
3k4dd
3k moves the cursor 3 lines up and 4 dd deletes the required 4 lines

Filter rows based on Color

Sometimes we work with excel sheets that have color coded rows and we often want to look at only a particular color at a time.

Following vba code does just that. Select any one cell of the color that you want to see. And run the macro filter_on_color. Make sure that column EZ is blank.

Sub filter_on_color()
' Select any color based on which to filter the sheet
' make sure EZ is empty

colindex = Selection.Cells.Interior.ColorIndex
col = ColumnLetter(Selection.Column)
lastrowcnt = Cells(Cells.Rows.Count, "A").End(xlUp).Row

MsgBox "Filtering on cell " & (col & Selection.Row)

For i = 1 To lastrowcnt
If Range(col & i).Cells.Interior.ColorIndex = colindex _ 
Then
Range("EZ" & i) = "Filter on color"
Else
Range("EZ" & i) = ""
End If
Next i

ActiveSheet.Select
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=156,  _ 
Criteria1:="Filter on color"
Range("A1").Select

End Sub

---------------------------------------------------------------------------------------

Function ColumnLetter(ColumnNumber As Integer) As String
' This function is taken from 
' http://www.freevbcode.com/ShowCode.asp?ID=4303

If ColumnNumber > 26 Then

' 1st character: Subtract 1 to map the characters to 0-25,
' but you don't have to remap back to 1-26
' after the 'Int' operation since columns
' 1-26 have no prefix letter

' 2nd character: Subtract 1 to map the characters to 0-25,
' but then must remap back to 1-26 after
' the 'Mod' operation by adding 1 back in
' (included in the '65')

ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
Chr(((ColumnNumber - 1) Mod 26) + 65)
Else
' Columns A-Z
ColumnLetter = Chr(ColumnNumber + 64)
End If
End Function

change cases

:%s/[a-z]/\U&/g

:%s/[A-Z]/\L&/g

sql function : NVL can be used to provide a default value when a field is null.

if salary = Null
then salary = 0
else
salary = emp_salary from emp_table
end if

This can be reduced to
select nvl(emp_salary,0) from emp_table;

---------------------------------------------------------------------------

sql function : DECODE can be used instead of a nested if condition or a case statement.

DECODE(expression or column,if_value,then_return_this_value,[ if_value,then_return_this_value,...],default_return_value)

e.g.
Following sql will assign state codes for Washington or Ohio and default to Florida if the state is neither of the two.
select emp_id, decode(emp_state,'Washington','WA','Ohio','OH','FL')
from emp_table;

Following sql will assign tax percentage as 30 if salary >=60000 else 20.
select emp_id, decode(emp_tax_pct,emp_salary >= 60000,30,20)
from emp_table;

awk world

awk executes the instructions for each line of input

$ cat testfile
1
2
3
4

$ awk '{print $1}' testfile
1
2
3
4

$ awk '{print "Hello, World"}' testfile
Hello, World
Hello, World
Hello, World
Hello, World

awk will wait for the input file name and will consider 'ENTER or RETURN' as valid input.

prefixing BEGIN keyword to any script lets awk execute the instructions without waiting for input file.

$ awk 'BEGIN {print "Hello, World"}' <== No input file
Hello, World

This instruction would be executed only once at the beginining.

$ awk 'BEGIN {print "Hello, World"}' testfile
Hello, World

$ awk 'BEGIN {print "Hello, World"} {print $1}' testfile
Hello, World
1
2
3
4


END keyword can be used to add a closing instruction.

$ awk 'BEGIN {print "Hello, World"} {print $1} END {print "Good bye"} ' testfile
Hello, World
1
2
3
4
Good bye

#

display only desired number (n) of rows in sql result

 

select * from table_name where rownum < n;

 

 

#

Available packages/ ports on MacPorts :
http://www.macports.org/ports.php?by=category&substr=archivers

To install a package using MacPorts :
sudo port install <package name>

sudo port uninstall <package name>

Caret M or ^M creeps into files imported to unix from windows/dos. It is a bad line break or carriage return. It can be seen in vim.

Caret M is a single character that can be created by pressing control+v and control+m. Writing Shift+6M would not work.

To remove caret M from the files give "%s/^M//g" on vi in escape mode.

IE6 bugs - float:left and png transparency

My html code in IE6 was not working right while Firefox worked fine. There were two issues -

 

png files were not displayed with transparent background instead a solid color.

Fixed it by adding javascript code in head. Credit to tech_support @ http://www.dynamicdrive.com/forums/showthread.php?t=22176

 

float:left with the image was not working. The image continued to be above the text rather than on left of it.

Fixed it by

1. Using "display:inline" with the text; and

2. Setting height for the div since the same image on Firefox slopped after doing #1.

 

 

#

 

while editing a file in vim, to do any of the following, get into vim command mode.

1. invoke shell using :sh. Return to vim using exit in shell.
2. see output of a vim command using :!cmd
3. insert the output of a vim command using :!rcmd
this last command is highly useful if we have to insert data from a different file into the current. it saves copy/paste efforts.