Sorting arrays in Javascript

Let’s suppose we have a simple array containing 5 strings. The .sort() method sorts them in ascending order and the .reverse() method sorts them in descending order, as demonstrated below.

var simpleArray1 = ["Paul", "Peter", "Mary"];

// .sort() will sort the array to become "Mary", "Paul", and then "Peter".
simpleArray1.sort();

// .reverse() will sort the array to become "Peter", "Paul", and then "Mary".
simpleArray1.reverse();

In the case of numbers, we should be aware that they are sorted in a similar fashion as strings. In the following example, 100 appears before 32 when using .sort() because 1 comes before 3.

var simpleArray2 = [100, 999, 32];

// .sort() will sort the array to become 100, 32, and then 999.
simpleArray2.sort();

// .reverse() will sort the array to become 999, 32, and then 100.
simpleArray2.reverse();

To sort an array of numbers by their numeric values, we should pass in a function into the .sort() method as follows.

// .sort(function(a, b) {return a - b}) will sort the array to become 32, 100, and then 999.
simpleArray2.sort(function(a, b) {return a - b});

// .sort(function(a, b) {return b - a}) will sort the array to become 999, 100, and then 32.
simpleArray2.sort(function(a, b) {return b - a});

We can sort an array of objects, ie. each element of the array has its own set of properties, on any of the properties as well. In the following example, each object in the array has a property called “id”, which we are using to sort.

// Ascending order
complexArray.sort(
	function(a, b) {
		return (a.id > b.id ? 1 : (a.id < b.id ? -1 : 0));
	} 
);

// Descending order
complexArray.sort(
	function(a, b) {
		return (a.id > b.id ? -1 : (a.id < b.id ? 1 : 0));
	} 
);

Prevent Excel from recognizing a string as a number when opening CSV files

I often use CSV format to transport data, largely because its text nature makes the data extremely portable across different platforms and applications. Yesterday when a colleague opened a CSV file from me using Excel, one of the fields I provided was displayed incorrectly. Two examples of the troubles Excel are listed below.

Value Provided in CSV Shown by Excel
0E36 0
0055 55

In the first case, Excel thought the value was a scientific notion; in the second case, Excel treated it as a number. In both cases, they were both wrong.

This issue also happens frequently when displaying ZIP codes for addresses in the United States, eg. Excel showing the intended “04001” as “4001”.

Because this is an Excel-specific behavior, I consider the following fix to be a work-around rather than a permanent solution; nevertheless, it works well for me. Instead of a simple CSV format as I had originally:

“aaa”,”bbb”,”ccc”,“0055”,”ddd”

I inserted an equal sign in front of it. This forces Excel to think that I am writing a formula that outputs a string, thus solving the Excel issue.

“aaa”,”bbb”,”ccc”,=”0055″,”ddd”

Creating a Queue in Oracle

For this particular example, let us assume that the information being between application takes on the form of two fields, row_id and row_status. Given that, we will create this following object type, which will be used by the queue shortly.

create or replace type obj_stuff as object (
	row_id	number,
	row_status	varchar2(10)
);

With the object type created, we will first create a queue table based on this object type, followed by a queue.

declare
begin
	dbms_aqadm.create_queue_table (
		queue_table	=>	'myschema.stuff_queue_table',
		queue_payload_type	=>	'myschema.obj_stuff'
	);
	dbms_aqadm.create_queue (
		queue_name	=>	'myschema.stuff_queue',
		queue_table	=>	'myschema.stuff_queue_table'
	);
end;

With the script above, we now have the queue table and the queue set up. To start the queue, use the .start_queue script, as below.

declare
begin
	dbms_aqadm.start_queue (
		queue_name	=>	'myschema.stuff_queue',
		enqueue	=>	true
	);
end;

Calculating the difference between two dates or times in DB2

To do so, we can utilize the timestampdiff() function provided by DB2. This function takes in two parameters. The first parameter is a numeric value indicating the unit in which you wish to receive the results in; the values must be one of the following.

  • 1 : Fractions of a second
  • 2 : Seconds
  • 4 : Minutes
  • 8 : Hours
  • 16 : Days
  • 32 : Weeks
  • 64 : Months
  • 128 : Quarters of a year
  • 256 : Years

The second parameter should contain the subtraction formula between two timestamps, with the results converted to character format. Below is one example of usage.

select 
timestampdiff(
  16, 
  char(timestamp('2010-01-11-15.01.33.453312') - current timestamp))  
from sysibm.sysdummy1;

The result from this statement is 210 at the time of the writing. Notice that even though the first timestamp is set to be prior than the current timestamp, the outcome is still positive — This function returns the absolute value (ie. always positive) reflecting the difference in time between two timestamps. Also, take note that the result will always be an integer, thus it can only be considered an estimation of the date/time difference rather than an exact one.

Using dummy table to select pseudocolumns

In Oracle, the dummy table is “dual”. It is present in all Oracle database installations, thus no special setup is needed to use it. Below are some samples on how to use this table.

-- Getting the current system date:

PROMPT> select sysdate from dual;

SYSDATE
---------
06-AUG-10

-- Getting the current logged-in user:

PROMPT> select user from dual;

USER
------------------------------
YTCDBA

-- Display the result of some string manipulation

PROMPT> select substr('hello world',1,7) from dual;

SUBSTR(
-------
hello w

The DB2 equivalent of Oracle’s “dual” is “sysibm.sysdummy1”. Below is a sample of the usage.

-- Getting the current system date:

PROMPT> select current date from sysibm.sysdummy1;

00001
----------
2010-08-06

-- Getting the current logged-in user:

PROMPT> select current user from sysibm.sysdummy1;

00001
-----
USER1

-- Display the result of some string manipulation

PROMPT> select substr('hello world',1,7) from sysibm.sysdummy1;

00001
-------
hello w

It is also worthy of note that MySQL and SQL Server can select pseudocolumn data without the use of a dummy table. For example, in SQL Server, you can get the current date by running “select getdate();” without a “from” clause in your SQL statement.

Java method to convert a string to proper or initial case

Example: toProperCase(“hello john smith”) will return “Hello John Smith”.

public static String toProperCase(String inputString) {
	String ret = "";
	StringBuffer sb = new StringBuffer();
	Matcher match = Pattern.compile("([a-z])([a-z]*)", Pattern.CASE_INSENSITIVE).matcher(inputString);
	while (match.find()) {
		match.appendReplacement(sb, match.group(1).toUpperCase() + match.group(2).toLowerCase()) ;
	}
	ret = match.appendTail(sb).toString();
	return ret;
}

Java method to round a number to specified decimal places

Example: roundToDecimals(1.23456, 2) will return 1.23.

public static float roundToDecimals(float fl, int roundToThisManyDecimalPoints) {
	float ret = 0;
	float pow = (float) Math.pow(10, roundToThisManyDecimalPoints);
	fl = fl * pow;
	float tmpFl = Math.round(fl);
	ret = (float) tmpFl / pow;
	return ret;
}

Manually incrementing an Oracle sequence by a defined amount

Let’s say you have a sequence object created called myschema.seq_mysequence which has the next value of 3000 and it normally increments 1 at a time, and let’s say that you need to reserve all numbers between 3000 and 4000 for a special purpose, thus you need to manually advance the sequence from 3000 to 4000. This is how you can increment the sequence 1000:

-- First, alter the object so the next increment will jump 1000 instead of just 1.
alter sequence myschema.seq_mysequence increment by 1000;

-- Run a select to actually increment it by 1000
select myschema.seq_mysequence.nextval from dual;

-- Alter the object back to incrementing only 1 at a time
alter sequence myschema.seq_mysequence increment by 1;

Javascript delay function

The sample Javascript delay() function below performs a delay for X milliseconds. During the time specified, users can proceed to perform actions on the particular web page.

Quick reminder: 1000 milliseconds = 1 second, 60000 milliseconds = 1 minute

function delay(milliseconds) {
	var cmd = 'alert("Hello World!  This should have appeared after ' + milliseconds + ' milliseconds.");';
	setTimeout(cmd, milliseconds);
}

Below is a live example, followed by the HTML/Javascript code used.



<script type="text/javascript">
function delay(milliseconds) {
	var cmd = 'alert("Hello World!  This should have appeared after ' + milliseconds + ' milliseconds.");';
	setTimeout(cmd, milliseconds);
}
</script>
<input type="button" onclick="javascript: delay(3000);" value="Do a 3-second delay">

If you are looking for a way to “freeze” the browser during the wait time, try the Javascript pause() method.

How to use the ampersand (&) symbol in SQL statements as a literal value

Because the ampersand symbol, “&”, denotes the start of a variable, you might be caught off guard when you are trying to use it as a literal value. For example:

SQL> select 'I like fish & chips' as x from dual;

Enter value for chips: old   1: select 'I like fish & chips' as x from dual
new   1: select 'I like fish ' as x from dual

X
------------
I like fish

1 row selected.

Of course, this is not what what you intended; you meant for the literal string to appear as “I like fish & chips”. There are two ways for you to get around this problem. The first method is to make sure the ampersand symbol is at the end of a string, thus requiring you to break up your sentence in one of the two following ways.

SQL> select 'I like fish &' || ' chips' as x from dual;

X
-------------------
I like fish & chips

1 row selected.

SQL> select concat('I like fish &',' chips') as x from dual;

X
-------------------
I like fish & chips

1 row selected.

The second method is to escape the ampersand symbol in the following manner.

SQL> set escape on;
SQL> select 'I like fish & chips' as x from dual;

X
-------------------
I like fish & chips

1 row selected.