Oracle NVL equivalent in MySQL

Read – What is Oracle NVL

Many Oracle Developers who write code in MySQL will look for this solution.

Answer 1 :- IFNULL

Answer 2 :- COALESCE ( It returns first not null expression in the list)

IFNULL

mysql> select IFNULL(null,1);
will return 1

COALESCE

mysql> select COALESCE(null,1);
will return 1

Note:- COALESCE will work in Oracle too.

MySQL SUBSTR function

SUBSTR function returns the sub string within a string.

Syntax:

SUBSTR(string, start_position, length)

or

SUBSTRING (string, start_position, length)

In MySQL both SUBSTR and SUBSTRING will work. SUBSTR is in ANSI standard.

Examples:-

mysql> select SUBSTR(‘sqlandplsql.com’,1,11);
will return  –  sqlandplsql

mysql> select SUBSTRING(‘sqlandplsql.com’,1,11);
will return  –  sqlandplsql

mysql> select SUBSTR(‘sqlandplsql.com’,2);
will return  –  qlandplsql
If you omit the length it will default to full string length.

mysql> select SUBSTR(‘sqlandplsql.com’,-3);
will return  –  com

Some more examples

select substr(‘sqlandplsql.com’,1,null); —> null
select substr(‘sqlandplsql.com’,null,null); —> null
select substr(‘sqlandplsql.com’,0,0); —>
select substr(‘sqlandplsql.com’,1,-2); —>
select substr(‘sqlandplsql.com’,0,3); —>

Install LAMP on Ubuntu Desktop

Step by step instructions for Installing LAMP on Ubuntu Desktop

LAMP stands for Linux (Ubuntu operating system), Apache ( Web server ), MySQL ( Database ), Php or Perl or Python ( Programming Language).  By default LAMP consists Php.
I have used Ubuntu 12.04 ( Precise Pangolin ) Desktop.LAMP installation in linux distro is pretty straight forward.

Pre-requisites

1. Sudo privileges to install
2. Internet connection

Step 1 –  Open a terminal

Step 2 –  Installation

Enter below command

$ sudo apt-get install lamp-server^  

( Note that caret(^) is required. Caret denotes it is part of tasksel packages)
It will prompt for MySQL administration password. Please remember the password. We need it to log into mysql.
After a few LAMP installation completed.
Following softwares installaed

1. Apache 2.2.22
2. Php 5.3.10
3. MySQL 5.5.22

Step 3 – Find and verify the versions installed

a) Verify Apache version

$ apache2 -v
Server version: Apache/2.2.22 (Ubuntu)
Server built:   Feb 13 2012 01:37:27

b) Verify Php version

$ php -version
PHP 5.3.10-1ubuntu3.1 with Suhosin-Patch (cli) (built: May  4 2012 02:21:57)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies

c) Verify MySQL version

$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 55
Server version: 5.5.22-0ubuntu1 (Ubuntu)

Step 4 –  Connect to MySQL

Enter the below command and provide the password we supplied at the time of installation.

$ mysql -b -h localhost -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.5.22-0ubuntu1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+—————————+
| Tables_in_mysql           |
+—————————+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+—————————+
24 rows in set (0.01 sec)

Step 5 – Test Apache installation

a) Open a web browser

b) Enter below url

http://localhost

If browser displays below texts then Apache is working is fine

It works!

This is the default web page for this server.

The web server software is running but no content has been added, yet.

You can start building websites using LAMP packages.

Step 6 – Test Php installation

Open a text editor and enter below text and save as “one.php”

<?php
echo “http://sqlandplsql.com&#8221;;
?>

Now open a browser and type below url and press enter or refresh

http://localhost.one.php

see the result as shown