Technologies Solutions: January 2012

Monday, January 9, 2012

MySQL function to search and replace HTML Entities with international characters

Attached is a MySQL function I've written to allow a MySQL SELECT statement to identify HTML Entities within the data and replace them with the relevant symbol, reserved or accented character, the function replaces all the HTML Entity Names with the relevant LATIN1 characters.

mysql> SELECT HTML_UnEncode('Dr.Hübner');
+---------------------------------+
| HTML_UnEncode('Dr.Hübner') |
+---------------------------------+
| Dr.Hübner |
+---------------------------------+
1 row in set (0.00 sec)

I am using it to process data captured by a web-based PHP application, although it's straightforward to remove these values as part of a data transfer exercise I couldn't find an easier way to handle HTML entities within a SELECT statement.

I'm posting the function in case you are struggling with the same problem but also in the hope that you might be able to improve on it. If you think it can be improved on please post your suggestions below.

In practise I use REGEXP to test for the occurrence of an HTML Entity before calling the function when one is present:

SELECT
deliveryReference AS Reference,
# Test for accented characters
CASE WHEN O.deliveryName REGEXP '&[^\s]*;'
THEN HTML_UnEncode(O.deliveryName)
ELSE O.deliveryName END AS Contact,
.......

The function is below, it should be created in the source database from which the data is being drawn:

DELIMITER $$

DROP FUNCTION IF EXISTS `sourcedatabase`.`HTML_UnEncode`$$
CREATE DEFINER=`user`@`localhost` FUNCTION `HTML_UnEncode`(x VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
BEGIN

DECLARE TextString VARCHAR(255) ;
SET TextString = x ;

#quotation mark
IF INSTR( x , '"' )
THEN SET TextString = REPLACE(TextString, '"','"') ;
END IF ;

#apostrophe
IF INSTR( x , ''' )
THEN SET TextString = REPLACE(TextString, ''','"') ;
END IF ;

#ampersand
IF INSTR( x , '&' )
THEN SET TextString = REPLACE(TextString, '&','&') ;
END IF ;

#less-than
IF INSTR( x , '<' )
THEN SET TextString = REPLACE(TextString, '<','<') ;
END IF ;

#greater-than
IF INSTR( x , '>' )
THEN SET TextString = REPLACE(TextString, '>','>') ;
END IF ;

#non-breaking space
IF INSTR( x , ' ' )
THEN SET TextString = REPLACE(TextString, ' ',' ') ;
END IF ;

#inverted exclamation mark
IF INSTR( x , '¡' )
THEN SET TextString = REPLACE(TextString, '¡','¡') ;
END IF ;

#cent
IF INSTR( x , '¢' )
THEN SET TextString = REPLACE(TextString, '¢','¢') ;
END IF ;

#pound
IF INSTR( x , '£' )
THEN SET TextString = REPLACE(TextString, '£','£') ;
END IF ;

#currency
IF INSTR( x , '¤' )
THEN SET TextString = REPLACE(TextString, '¤','¤') ;
END IF ;

#yen
IF INSTR( x , '¥' )
THEN SET TextString = REPLACE(TextString, '¥','¥') ;
END IF ;

#broken vertical bar
IF INSTR( x , '¦' )
THEN SET TextString = REPLACE(TextString, '¦','¦') ;
END IF ;

#section
IF INSTR( x , '§' )
THEN SET TextString = REPLACE(TextString, '§','§') ;
END IF ;

#spacing diaeresis
IF INSTR( x , '¨' )
THEN SET TextString = REPLACE(TextString, '¨','¨') ;
END IF ;

#copyright
IF INSTR( x , '©' )
THEN SET TextString = REPLACE(TextString, '©','©') ;
END IF ;

#feminine ordinal indicator
IF INSTR( x , 'ª' )
THEN SET TextString = REPLACE(TextString, 'ª','ª') ;
END IF ;

#angle quotation mark (left)
IF INSTR( x , '«' )
THEN SET TextString = REPLACE(TextString, '«','«') ;
END IF ;

#negation
IF INSTR( x , '¬' )
THEN SET TextString = REPLACE(TextString, '¬','¬') ;
END IF ;

#soft hyphen
IF INSTR( x , '' )
THEN SET TextString = REPLACE(TextString, '','') ;
END IF ;

#registered trademark
IF INSTR( x , '®' )
THEN SET TextString = REPLACE(TextString, '®','®') ;
END IF ;

#spacing macron
IF INSTR( x , '¯' )
THEN SET TextString = REPLACE(TextString, '¯','¯') ;
END IF ;

#degree
IF INSTR( x , '°' )
THEN SET TextString = REPLACE(TextString, '°','°') ;
END IF ;

#plus-or-minus
IF INSTR( x , '±' )
THEN SET TextString = REPLACE(TextString, '±','±') ;
END IF ;

#superscript 2
IF INSTR( x , '²' )
THEN SET TextString = REPLACE(TextString, '²','²') ;
END IF ;

#superscript 3
IF INSTR( x , '³' )
THEN SET TextString = REPLACE(TextString, '³','³') ;
END IF ;

#spacing acute
IF INSTR( x , '´' )
THEN SET TextString = REPLACE(TextString, '´','´') ;
END IF ;

#micro
IF INSTR( x , 'µ' )
THEN SET TextString = REPLACE(TextString, 'µ','µ') ;
END IF ;

#paragraph
IF INSTR( x , '¶' )
THEN SET TextString = REPLACE(TextString, '¶','¶') ;
END IF ;

#middle dot
IF INSTR( x , '·' )
THEN SET TextString = REPLACE(TextString, '·','·') ;
END IF ;

#spacing cedilla
IF INSTR( x , '¸' )
THEN SET TextString = REPLACE(TextString, '¸','¸') ;
END IF ;

#superscript 1
IF INSTR( x , '¹' )
THEN SET TextString = REPLACE(TextString, '¹','¹') ;
END IF ;

#masculine ordinal indicator
IF INSTR( x , 'º' )
THEN SET TextString = REPLACE(TextString, 'º','º') ;
END IF ;

#angle quotation mark (right)
IF INSTR( x , '»' )
THEN SET TextString = REPLACE(TextString, '»','»') ;
END IF ;

#fraction 1/4
IF INSTR( x , '¼' )
THEN SET TextString = REPLACE(TextString, '¼','¼') ;
END IF ;

#fraction 1/2
IF INSTR( x , '½' )
THEN SET TextString = REPLACE(TextString, '½','½') ;
END IF ;

#fraction 3/4
IF INSTR( x , '¾' )
THEN SET TextString = REPLACE(TextString, '¾','¾') ;
END IF ;

#inverted question mark
IF INSTR( x , '¿' )
THEN SET TextString = REPLACE(TextString, '¿','¿') ;
END IF ;

#multiplication
IF INSTR( x , '×' )
THEN SET TextString = REPLACE(TextString, '×','×') ;
END IF ;

#division
IF INSTR( x , '÷' )
THEN SET TextString = REPLACE(TextString, '÷','÷') ;
END IF ;

#capital a, grave accent
IF INSTR( x , 'À' )
THEN SET TextString = REPLACE(TextString, 'À','À') ;
END IF ;

#capital a, acute accent
IF INSTR( x , 'Á' )
THEN SET TextString = REPLACE(TextString, 'Á','Á') ;
END IF ;

#capital a, circumflex accent
IF INSTR( x , 'Â' )
THEN SET TextString = REPLACE(TextString, 'Â','Â') ;
END IF ;

#capital a, tilde
IF INSTR( x , 'Ã' )
THEN SET TextString = REPLACE(TextString, 'Ã','Ã') ;
END IF ;

#capital a, umlaut mark
IF INSTR( x , 'Ä' )
THEN SET TextString = REPLACE(TextString, 'Ä','Ä') ;
END IF ;

#capital a, ring
IF INSTR( x , 'Å' )
THEN SET TextString = REPLACE(TextString, 'Å','Å') ;
END IF ;

#capital ae
IF INSTR( x , 'Æ' )
THEN SET TextString = REPLACE(TextString, 'Æ','Æ') ;
END IF ;

#capital c, cedilla
IF INSTR( x , 'Ç' )
THEN SET TextString = REPLACE(TextString, 'Ç','Ç') ;
END IF ;

#capital e, grave accent
IF INSTR( x , 'È' )
THEN SET TextString = REPLACE(TextString, 'È','È') ;
END IF ;

#capital e, acute accent
IF INSTR( x , 'É' )
THEN SET TextString = REPLACE(TextString, 'É','É') ;
END IF ;

#capital e, circumflex accent
IF INSTR( x , 'Ê' )
THEN SET TextString = REPLACE(TextString, 'Ê','Ê') ;
END IF ;

#capital e, umlaut mark
IF INSTR( x , 'Ë' )
THEN SET TextString = REPLACE(TextString, 'Ë','Ë') ;
END IF ;

#capital i, grave accent
IF INSTR( x , 'Ì' )
THEN SET TextString = REPLACE(TextString, 'Ì','Ì') ;
END IF ;

#capital i, acute accent
IF INSTR( x , 'Í' )
THEN SET TextString = REPLACE(TextString, 'Í','Í') ;
END IF ;

#capital i, circumflex accent
IF INSTR( x , 'Î' )
THEN SET TextString = REPLACE(TextString, 'Î','Î') ;
END IF ;

#capital i, umlaut mark
IF INSTR( x , 'Ï' )
THEN SET TextString = REPLACE(TextString, 'Ï','Ï') ;
END IF ;

#capital eth, Icelandic
IF INSTR( x , 'Ð' )
THEN SET TextString = REPLACE(TextString, 'Ð','Ð') ;
END IF ;

#capital n, tilde
IF INSTR( x , 'Ñ' )
THEN SET TextString = REPLACE(TextString, 'Ñ','Ñ') ;
END IF ;

#capital o, grave accent
IF INSTR( x , 'Ò' )
THEN SET TextString = REPLACE(TextString, 'Ò','Ò') ;
END IF ;

#capital o, acute accent
IF INSTR( x , 'Ó' )
THEN SET TextString = REPLACE(TextString, 'Ó','Ó') ;
END IF ;

#capital o, circumflex accent
IF INSTR( x , 'Ô' )
THEN SET TextString = REPLACE(TextString, 'Ô','Ô') ;
END IF ;

#capital o, tilde
IF INSTR( x , 'Õ' )
THEN SET TextString = REPLACE(TextString, 'Õ','Õ') ;
END IF ;

#capital o, umlaut mark
IF INSTR( x , 'Ö' )
THEN SET TextString = REPLACE(TextString, 'Ö','Ö') ;
END IF ;

#capital o, slash
IF INSTR( x , 'Ø' )
THEN SET TextString = REPLACE(TextString, 'Ø','Ø') ;
END IF ;

#capital u, grave accent
IF INSTR( x , 'Ù' )
THEN SET TextString = REPLACE(TextString, 'Ù','Ù') ;
END IF ;

#capital u, acute accent
IF INSTR( x , 'Ú' )
THEN SET TextString = REPLACE(TextString, 'Ú','Ú') ;
END IF ;

#capital u, circumflex accent
IF INSTR( x , 'Û' )
THEN SET TextString = REPLACE(TextString, 'Û','Û') ;
END IF ;

#capital u, umlaut mark
IF INSTR( x , 'Ü' )
THEN SET TextString = REPLACE(TextString, 'Ü','Ü') ;
END IF ;

#capital y, acute accent
IF INSTR( x , 'Ý' )
THEN SET TextString = REPLACE(TextString, 'Ý','Ý') ;
END IF ;

#capital THORN, Icelandic
IF INSTR( x , 'Þ' )
THEN SET TextString = REPLACE(TextString, 'Þ','Þ') ;
END IF ;

#small sharp s, German
IF INSTR( x , 'ß' )
THEN SET TextString = REPLACE(TextString, 'ß','ß') ;
END IF ;

#small a, grave accent
IF INSTR( x , 'à' )
THEN SET TextString = REPLACE(TextString, 'à','à') ;
END IF ;

#small a, acute accent
IF INSTR( x , 'á' )
THEN SET TextString = REPLACE(TextString, 'á','á') ;
END IF ;

#small a, circumflex accent
IF INSTR( x , 'â' )
THEN SET TextString = REPLACE(TextString, 'â','â') ;
END IF ;

#small a, tilde
IF INSTR( x , 'ã' )
THEN SET TextString = REPLACE(TextString, 'ã','ã') ;
END IF ;

#small a, umlaut mark
IF INSTR( x , 'ä' )
THEN SET TextString = REPLACE(TextString, 'ä','ä') ;
END IF ;

#small a, ring
IF INSTR( x , 'å' )
THEN SET TextString = REPLACE(TextString, 'å','å') ;
END IF ;

#small ae
IF INSTR( x , 'æ' )
THEN SET TextString = REPLACE(TextString, 'æ','æ') ;
END IF ;

#small c, cedilla
IF INSTR( x , 'ç' )
THEN SET TextString = REPLACE(TextString, 'ç','ç') ;
END IF ;

#small e, grave accent
IF INSTR( x , 'è' )
THEN SET TextString = REPLACE(TextString, 'è','è') ;
END IF ;

#small e, acute accent
IF INSTR( x , 'é' )
THEN SET TextString = REPLACE(TextString, 'é','é') ;
END IF ;

#small e, circumflex accent
IF INSTR( x , 'ê' )
THEN SET TextString = REPLACE(TextString, 'ê','ê') ;
END IF ;

#small e, umlaut mark
IF INSTR( x , 'ë' )
THEN SET TextString = REPLACE(TextString, 'ë','ë') ;
END IF ;

#small i, grave accent
IF INSTR( x , 'ì' )
THEN SET TextString = REPLACE(TextString, 'ì','ì') ;
END IF ;

#small i, acute accent
IF INSTR( x , 'í' )
THEN SET TextString = REPLACE(TextString, 'í','í') ;
END IF ;

#small i, circumflex accent
IF INSTR( x , 'î' )
THEN SET TextString = REPLACE(TextString, 'î','î') ;
END IF ;

#small i, umlaut mark
IF INSTR( x , 'ï' )
THEN SET TextString = REPLACE(TextString, 'ï','ï') ;
END IF ;

#small eth, Icelandic
IF INSTR( x , 'ð' )
THEN SET TextString = REPLACE(TextString, 'ð','ð') ;
END IF ;

#small n, tilde
IF INSTR( x , 'ñ' )
THEN SET TextString = REPLACE(TextString, 'ñ','ñ') ;
END IF ;

#small o, grave accent
IF INSTR( x , 'ò' )
THEN SET TextString = REPLACE(TextString, 'ò','ò') ;
END IF ;

#small o, acute accent
IF INSTR( x , 'ó' )
THEN SET TextString = REPLACE(TextString, 'ó','ó') ;
END IF ;

#small o, circumflex accent
IF INSTR( x , 'ô' )
THEN SET TextString = REPLACE(TextString, 'ô','ô') ;
END IF ;

#small o, tilde
IF INSTR( x , 'õ' )
THEN SET TextString = REPLACE(TextString, 'õ','õ') ;
END IF ;

#small o, umlaut mark
IF INSTR( x , 'ö' )
THEN SET TextString = REPLACE(TextString, 'ö','ö') ;
END IF ;

#small o, slash
IF INSTR( x , 'ø' )
THEN SET TextString = REPLACE(TextString, 'ø','ø') ;
END IF ;

#small u, grave accent
IF INSTR( x , 'ù' )
THEN SET TextString = REPLACE(TextString, 'ù','ù') ;
END IF ;

#small u, acute accent
IF INSTR( x , 'ú' )
THEN SET TextString = REPLACE(TextString, 'ú','ú') ;
END IF ;

#small u, circumflex accent
IF INSTR( x , 'û' )
THEN SET TextString = REPLACE(TextString, 'û','û') ;
END IF ;

#small u, umlaut mark
IF INSTR( x , 'ü' )
THEN SET TextString = REPLACE(TextString, 'ü','ü') ;
END IF ;

#small y, acute accent
IF INSTR( x , 'ý' )
THEN SET TextString = REPLACE(TextString, 'ý','ý') ;
END IF ;

#small thorn, Icelandic
IF INSTR( x , 'þ' )
THEN SET TextString = REPLACE(TextString, 'þ','þ') ;
END IF ;

#small y, umlaut mark
IF INSTR( x , 'ÿ' )
THEN SET TextString = REPLACE(TextString, 'ÿ','ÿ') ;
END IF ;

RETURN TextString ;

END$$

DELIMITER ;

Saturday, January 7, 2012

Common Ubuntu vi editor command list

I would like to share some commonly used vi editor command which will be useful for the beginners who love to work in vi editor.
This will be more useful for new linux users.

Purpose	vi Command
To insert new text	esc + i ( You have to press ‘escape’ key then ‘i’)
To save file	esc + : + w (Press ‘escape’ key then ‘colon’ and finally ‘w’)
To save file with file name (save as)	esc + : + w “filename”
To quit the vi editor	esc + : + q
To quit without saving	esc + : + q!
To save and quit vi editor	esc + : + wq
To search for specified word in forward direction	esc + /word (Press ‘escape’ key, type /word-to-find, for e.g. to find word ‘work‘, type as /work)
To continue with search	n
To search for specified word in backward direction	esc + ?word (Press ‘escape’ key, type word-to-find)
To copy the line where cursor is located	esc + yy
To paste the text just deleted or copied at the cursor	esc + p
To delete entire line where cursor is located	esc + dd
To delete word from cursor position	esc + dw
To Find all occurrence of given word and Replace then globally without confirmation	esc + :$s/word-to-find/word-to-replace/g For. e.g. :$s/work/workers/g Here word “work” is replace with “workers”
To Find all occurrence of given word and Replace then globally with confirmation	esc + :$s/word-to-find/word-to-replace/cg
To run shell command like ls, cp or date etc within vi	esc + :!shell-command For e.g. :!pwd

Basic "vi" features
One edits a file in vi by issuing the command: vi file-to-edit.txt
The vi editor has three modes, command mode, insert mode and command line mode.

Command mode: letters or sequence of letters interactively command vi. Commands are case sensitive. The ESC key can end a command.
Insert mode: Text is inserted. The ESC key ends insert mode and returns you to command mode. One can enter insert mode with the "i" (insert), "a" (insert after), "A" (insert at end of line), "o" (open new line after current line) or "O" (Open line above current line) commands.
Command line mode: One enters this mode by typing ":" which puts the command line entry at the foot of the screen.

Partial list of interactive commands:
Cursor movement:

Keystrokes	Action
h/j/k/l	Move cursor left/down/up/right
spacebar	Move cursor right one space
-/+	Move cursor down/up in first column
ctrl-d	Scroll down one half of a page
ctrl-u	Scroll up one half of a page
ctrl-f	Scroll forward one page
ctrl-b	Scroll back one page
M (shift-h)	Move cursor to middle of page
H	Move cursor to top of page
L	Move cursor to bottom of page
W w 5w	Move cursor a word at a time Move cursor ahead 5 words
B b 5b	Move cursor back a word at a time Move cursor back a word at a time Move cursor back 5 words
e 5e	Move cursor to end of word Move cursor ahead to the end of the 5th word
0 (zero)	Move cursor to beginning of line
$	Move cursor to end of line
)	Move cursor to beginning of next sentence
(	Move cursor to beginning of current sentence
G	Move cursor to end of file
%	Move cursor to the matching bracket. Place cursor on {}[]() and type "%". Use the matchit or xmledit plug-in to extend this capability to XML/XHTML tags.
'.	Move cursor to previously modified line.
'a	Move cursor to line mark "a" generated by marking with keystroke "ma"
'A	Move cursor to line mark "a" (global between buffers) generated by marking with keystroke "mA"
]'	Move cursor to next lower case mark.
['	Move cursor to previous lower case mark.

Editing commands:

Keystrokes	Action
i	Insert at cursor
a	Append after cursor
A	Append at end of line
ESC	Terminate insert mode
u	Undo last change
U	Undo all changes to entire line
o	Open a new line
dd 3dd	Delete line Delete 3 lines.
D	Delete contents of line after cursor
C	Delete contents of line after cursor and insert new text. Press esc key to end insertion.
dw 4dw	Delete word Delete 4 words
cw	Change word
x	Delete character at cursor
r	Replace character
R	Overwrite characters from cursor onward
s	Substitute one character under cursor continue to insert
S	Substitute entire line and begin to insert at beginning of line
~	Change case of individual character
ctrl-a ctrl-x	Increment number under the cursor. Decrement number under the cursor.
/search_string{CR}	Search for search_string
?search_string{CR}	Search backwards (up in file) for search_string
/\<search_string\>{CR}	Search for search_word Ex: /\<s\> Search for variable "s" but ignore declaration "string" or words containing "s". This will find "string s;", "s = fn(x);", "x = fn(s);", etc
n	Find next occurrence of search_word
N	Find previous occurrence of search_word
.	repeat last command action.

Friday, January 6, 2012

Ubuntu Useful Commands

A comprehensive list of commands needed when using Ubuntu:

Command privileges.

    sudo command - run command as root
    sudo su – root shell open
    sudo su user – open shell as a user
    sudo -k – forget your password sudo
    gksudo command – sudo visual dialog (GNOME)
    kdesudo command – sudo visual dialog (KDE)
    sudo visudo – edit / etc / sudoers
    gksudo nautilus – root file manager (GNOME)
    kdesudo konqueror – root file manager (KDE)
    passwd – change your password

Command Network

    ifconfig – displays information network
    iwconfig – displays information from wireless
    sudo iwlist scan – scan wireless networks
    sudo /etc/init.d/networking restart – reset the network
    (file) /etc/network/interfaces – manual configuration
    ifup interface – bring online interface
    ifdown interface – disable interface

Commands Display

    sudo /etc/init.d/gdm restart – reset X (Gnome)
    sudo /etc/init.d/kdm restart – reset X (KDE)
    (file) /etc/X11/xorg.conf – show Configuration
    sudo dpkg-reconfigure - reconfigure xserver-xorg-phigh - reset configuration X
    Ctrl+Alt+Bksp – X display reset if frozen
    Ctrl+Alt+FN – switch to tty N
    Ctrl+Alt+F7 – switch back to X display

Commands Service System.

    start service – service to start work (Upstart)
    stop service – service to stop working (Upstart)
    status service – check if service is running (Upstart)
    /etc/init.d/service start – start service (SysV)
    /etc/init.d/service stop – stop service (SysV)
    /etc/init.d/service status – check service (SysV)
    /etc/init.d/service restart – reset service (SysV)
    runlevel – get current runlevel

Commands for Firewall.

    ufw enable – turn on the firewall
    ufw disable – turn off the firewall
    ufw default allow – allow all connections by default
    ufw default deny – drop all connections by default
    ufw status – current rules and
    ufw allow port – to allow traffic on port
    ufw deny port – port block
    ufw deny from ip – ip block

Command System.

    lsb_release -a – get the version of Ubuntu
    uname -r – get kernel version
    uname -a – get all the information kernel

Commands for Package Manager.

    apt-get update – refresh updates available
    apt-get upgrade – update all packages
    apt-get dist-upgrade – version update
    apt-get install pkg – installing pkg
    apt-get remove pkg – uninstall pkg
    apt-get autoremove – removing packages obsotletos
    apt-get -f install – try to fix packages
    dpkg –configure -a – try to fix a broken package
    dpkg -i pkg.deb – install file pkg.deb
    (file) /etc/apt/sources.list – list of repositories APT

Special Packages For commands.

    ubuntu-desktop – Setting the standard Ubuntu
    kubuntu-desktop – KDE Desktop
    xubuntu-desktop – desktop XFCE
    ubuntu-minimal – core earnings Ubuntu
    ubuntu-standard – the standard utilities Ubuntu
    ubuntu-restricted-extras – not free, but useful
    kubuntu-restricted-extras – ditto KDE
    xubuntu-restricted-extras – ditto XFCE
    build-essential – packages used to compile
    linux-image-generic – latest generic kernel image
    linux-headers-generic – latest headlines

Applications commands.

    nautilus – File Manager (GNOME)
    dolphin – File Manager (KDE)
    konqueror – Web browser (KDE)
    kate – text editor (KDE)
    gedit – text editor (GNOME)