Trim characters from a string in oracle

• For trimming characters from last 

select  substr('Trim_India_1', 0, length('Trim_India_1')-2) from dual;

Result: Trim_India

• For trimming characters from starting 

select  substr('Trim_India_1', 3) from dual;

Result: im_India_1

This will return all characters starting from position 3

UNIX Commands

Ten ESSENTIAL UNIX Commands

These are ten commands that you really need to know in order to get started with UNIX. They are probably similar to commands you already know for another operating system.

Command	Example	Description
1.     ls	ls ls -alF	Lists files in current directory List in long format
2.     cd	cd tempdir cd .. cd ~dhyatt/web-docs	Change directory to tempdir Move back one directory Move into dhyatt's web-docs directory
3.     mkdir	mkdir graphics	Make a directory called graphics
4.     rmdir	rmdir emptydir	Remove directory (must be empty)
5.     cp	cp file1 web-docs cp file1 file1.bak	Copy file into directory Make backup of file1
6.     rm	rm file1.bak rm *.tmp	Remove or delete file Remove all file
7.     mv	mv old.html new.html	Move or rename files
8.     more	more index.html	Look at file, one page at a time
9.     lpr	lpr index.html	Send file to printer
10.   man	man ls	Online manual (help) about command

 

 

Ten VALUABLE UNIX Commands

Once you have mastered the basic UNIX commands, these will be quite valuable in managing your own account.

Command	Example	Description
1.     grep <str><files>	grep "bad word" *	Find which files contain a certain word
2.     chmod <opt> <file>	chmod 644 *.html chmod 755 file.exe	Change file permissions read only Change file permissions to executable
3.     passwd	passwd	Change passwd
4.     ps <opt>	ps aux ps aux   |   grep dhyatt	List all running processes by #ID List process #ID's running by dhyatt
5.     kill <opt> <ID>	kill -9 8453	Kill process with ID #8453
6.     gcc (g++) <source>	gcc file.c -o file g++ fil2.cpp -o fil2	Compile a program written in C Compile a program written in C++
7.     gzip <file>	gzip bigfile gunzip bigfile.gz	Compress file Uncompress file
8.     mail         (pine)	mail me@tjhsst.edu < file1 pine	Send file1 by email to someone Read mail using pine
9.     telnet <host>         ssh <host>	telnet vortex.tjhsst.edu ssh -l dhyatt jazz.tjhsst.edu	Open a connection to vortex Open a secure connection to jazz as user dhyatt
10.   ftp <host> ncftp <host/directory>	ftp station1.tjhsst.edu ncftp metalab.unc.edu	Upload or Download files to station1 Connect to archives at UNC

 

Ten FUN UNIX Commands

These are ten commands that you might find interesting or amusing. They are actually quite helpful at times, and should not be considered idle entertainment.

Command	Example	Description
1.     who	who	Lists who is logged on your machine
2.     finger	finger	Lists who is on computers in the lab
3.     ytalk <user@place>	ytalk dhyatt@threat	Talk online with dhyatt who is on threat
4.     history	history	Lists commands you've done recently
5.     fortune	fortune	Print random humerous message
6.     date	date	Print out current date
7.     cal <mo> <yr>	cal 9 2000	Print calendar for September 2000
8.     xeyes	xeyes &	Keep track of cursor (in "background")
9.     xcalc	xcalc &	Calculator ("background" process)
10.   mpage <opt> <file>	mpage -8 file1   |  lpr	Print 8 pages on a single sheet and send to printer (the font will be small!)

 

Ten HELPFUL UNIX Commands

These ten commands are very helpful, especially with graphics and word processing type applications.

Command	Example	Description
1.     netscape	netscape &	Run Netscape browser
2.     xv	xv &	Run graphics file converter
3.     xfig / xpaint	xfig & (xpaint &)	Run drawing program
4.     gimp	gimp &	Run photoshop type program
5.     ispell <fname>	ispell file1	Spell check file1
6.     latex <fname>	latex file.tex	Run LaTeX, a scientific document tool
7.     xemacs / pico	xemacs (or pico)	Different editors
8.     soffice	soffice &	Run StarOffice, a full word processor
9.     m-tools (mdir, mcopy,         mdel, mformat, etc. )	mdir a: mcopy file1   a:	DOS commands from UNIX (dir A:) Copy file1 to A:
10.   gnuplot	gnuplot	Plot data graphically

 

Ten USEFUL UNIX Commands:

These ten commands are useful for monitoring system access, or simplifying your own environment.

Command	Example	Description
1.     df	df	See how much free disk space
2.     du	du -b subdir	Estimate disk usage of directory in Bytes
3.     alias	alias lls="ls -alF"	Create new command "lls" for long format of ls
4.     xhost	xhost + threat.tjhsst.edu xhost -	Permit window to display from x-window program from threat Allow no x-window access from other systems
5.     fold	fold -s file1   |   lpr	Fold or break long lines at 60 characters and send to printer
6.     tar	tar -cf subdir.tar subdir tar -xvf subdir.tar	Create an archive called subdir.tar of a directory Extract files from an archive file
7.     ghostview (gv)	gv filename.ps	View a Postscript file
8.     ping    (traceroute)	ping threat.tjhsst.edu traceroute www.yahoo.com	See if machine is alive Print data path to a machine
9.     top	top	Print system usage and top resource hogs
10.   logout (exit)	logout or exit	How to quit a UNIX shell.

 

How to Extract Pictures From An Excel File

If you want to quickly extract pictures from an Excel file that embedded many pictures, you cannot be simply to do this. There's no build-in function to do this in Excel.

 Here are two simple ways to quickly extract pictures from an Excel file.

1.  Change the save as type to *.htm, *.html

Open your Excel file, click File tab, choose Save As, in the Save As window, click the drop-down menu labeled Save as type:, scroll down and choose Web Page (*.htm;*.html), Click Save.

When a web page is saved in a computer two files are actually generated. One is the html file, and another is a folder that contains all the images and other component files that are a part of the web page. For example, an Excel file named Products.xls saved as a web page will generate Products.htm and a folder labeled Products_files. If Products.xlscontained images, these images will now be in the Products.xls_files folder. Now, open Products.xls_files folder, you will sell all the pictures.

2. Change the Excel file extension (only available with .xlsx) as a RAR file 

For example, change Products.xlsx to Products.rar, extract the Products.rar file, you will see 3 folders (_rels, docProps and xl) and 1 xml file ([Content_Types].xml), open xl folder, open media folder, you will sell all the images in the media folder.

How To Extract Formulas From Excel At Once

Sometimes you may need to convert formula to text string in Excel, such as =A1+B1, follow the below mentioned steps for extracting the formulas from Excel at once.

Example

For Example, we need to show the Column C with original formulas but not their calculated results in Column D, see the following screen shot.

Steps

1. select Column C.
2. Under the Data menu tab, in the Data Tools group, click the Text to Columns button.
3. In Convert Text to Columns Wizard window, do the following:
   1. In Convert Text to Columns Wizard - step 1 of 3 window, click Next.
   2. In Convert Text to Columns Wizard - step 2 of 3 window, click Next.
   3. In Convert Text to Columns Wizard - step 3 of 3 window, under Column data format, choose Text, in Destination box, enter a cell reference: D1, or click Collapse Button  to temporarily hide the dialog box, select cell D1 on the worksheet, and then press Expand Button .
   4. click Finish.

Final Results

Video

Quickly Copy Values Without Formulas in Excel

You can quickly copy values without formulas in Excel. 

The example may be easier to understand if you copy A1:C5 to a blank worksheet.

	A	B	C
1	Data	Formula	Result
2	3	=A2-1
3	4	=A3-1
4	5	=A4-1
5	6	=A5-1

Step 1: Select B2
Step 2: Press and hold CTRL then Select B3:B5
Step 3: Press CTRL C
Step 4: Select a blank cell (eg. C2) and Press CTRL V to paste the values.

Excel VBA Editor Keyboard Shortcuts

The complete list of Excel VBA Editor (VBE) keyboard shortcuts are for U.S. keyboard layout and Windows Operation system. 

#	Action	Shortcut
1	Help	F1
2	Object Browser	F2
3	Find	F3
4	Properies Window	F4
5	Run	F5
6	Switch Split Windows	F6
7	Goto Window Of Object	F7
8	Step	F8
9	Breakpoint	F9
10	Activate Menu Bar	F10
11	Toggle Insert Mode	Insert
12	Delete	Delete
13	Beginning Of Line	Home
14	End Of Line	End
15	Page Up	PgUp
16	Page Down	PgDn
17	Left	←
18	Right	→
19	Up	↑
20	Down	↓
21	Indent	Tab
22	New Line	Enter
23	Delete Prev Char	BackSpace
24	Procedure Definition	Shift F2
25	Find Prev	Shift F3
26	Find Next	Shift F4
27	Procedure Step	Shift F8
28	Quick Watch	Shift F9
29	Show Right Click Menu	Shift F10
30	Paste	Shift Insert
31	Select To Start Of Line	Shift Home
32	Select To End Of Line	Shift End
33	Select To Top Of Module	Shift PgUp
34	Select To End Of Module	Shift PgDn
35	Extend Selection Left 1 Char	Shift ←
36	Extend Selection Right 1 Char	Shift →
37	Extend Selection Up	Shift ↑
38	Extend Selection Down	Shift ↓
39	Un-indent	Shift Tab
40	Focus To Object Box	Ctrl F2
41	Close Window	Ctrl F4
42	Run To Cursor	Ctrl F8
43	Activate Menu Bar	Ctrl F10
44	Select All Text	Ctrl B
45	Copy	Ctrl C
46	Export Module	Ctrl E
47	Find	Ctrl F
48	Immediate Window	Ctrl G
49	Replace	Ctrl H
50	Turn On Quick Info	Ctrl I
51	List Members	Ctrl J
52	Show Call Stack	Ctrl L
53	Import File	Ctrl M
54	New Line	Ctrl N
55	Print	Ctrl P
56	Project Window	Ctrl R
57	Save	Ctrl S
58	Show Available Components	Ctrl T
59	Paste	Ctrl V
60	Cut	Ctrl X
61	Cut Entire Line	Ctrl Y
62	Undo	Ctrl Z
63	Copy	Ctrl Insert
64	Delete To End Of Word	Ctrl Delete
65	Top Of Module	Ctrl Home
66	End Of Module	Ctrl End
67	Top Of Current Procedure	Ctrl Page Up
68	End Of Current Procedure	Ctrl Page Down
69	Left Word	Ctrl Left Arrow
70	Right Word	Ctrl Right Arrow
71	Previous Procedure	Ctrl Up Arrow
72	Next Procedure	Ctrl Down Arrow
73	Turn On Complete Word	Ctrl Space Bar
74	Cycle Windows	Ctrl Tab
75	Delete To Start Of Word	Ctrl BackSpace
76	Close VBE	Alt F4
77	Run Error Handler	Alt F5
78	Switch Between Last 2 Windows	Alt F6
79	Step Error Handler	Alt F7
80	Return To Application	Alt F11
81	Add-Ins Menu	Alt A
82	Debug Menu	Alt D
83	Edit Menu	Alt E
84	File Menu	Alt F
85	Help Menu	Alt H
86	Insert Menu	Alt J
87	Format Menu	Alt O
88	Close & Return	Alt Q
89	Run Menu	Alt R
90	Tools Menu	Alt T
91	View Menu	Alt V
92	Window Menu	Alt W
93	System Menu	Alt Space Bar
94	Cycle Applications	Alt Tab
95	Undo	Alt BackSpace
96	Previous Position	Ctrl Shift F2
97	Clear All Breakpoints	Ctrl Shift F9
98	Turn On Param Info	Ctrl Shift I
99	Turn On List Properties	Ctrl Shift J