Monthly Archives: June 2014

Handling Null Values in SQL- ISNULL, NVL2, IFNULL, Coalesce

There can be situations when if a value is null in sql query, you want to replace it with something else. Different DBs provide you different options

Oracle- PLSQL
select NVL2(teamid, teamid, deptid) allocationid, empid, name from employee; //If team id is available that is returned as allocation id else deptid

IN MS-SQL we have ISNULL
select ISNULL(teamid, deptid) allocationid, empid, name from employee;

Similarly MySQL has IFNULL

Another option is COALESCE, this works for both Oracle and MS-SQL. This is slightly different from others in implementation that it makes columns to be skipped unless first non-null value is found

select COALESCE(teamid, deptid, organizationid) allocationid, empid, name from employee;

Find Unique Rank for a String

Problem Statement: For each String we need to assign a unique rank.

B will Have higher rank than A
Z Will have higher rank than B
Z will have higher rank than AA
KAMAL will be higher than AMIT (K>A)
KAWAL will be higher than KAMAL (W>A)
and so on

Solution: Assign a numeric value to each character in string and then add them in a way to create a rank.
Value assigned to characters (A is lowest)
A=0.01, b=0.02.. and so on

Algorithm:
1. Loop The characters in string
1a. Assign Face weight to current character (A= 0.01, B= 0.02 etc)
1b. Assign Position Weight to current character (first char is multiple of 0.01, second of 0.0001 and so on)
1c. update totalweight by adding character weight calculated in 1a and 1b
2. Return totalweight

Code:
public static double getRank(String str)
{
str=str.toUpperCase();
char arr[]=str.toCharArray();
double rank=0;
double multiplier= 0.01;
for(int i=0;iAlgorithm Complexity:
Though if you look closely it might give impression of O(N) complexity, where N is characters in destination string. But as if are dealing with controlled input and string can be of average length 6-10 characters, we can easily confirm the algo to be of constant complexity.