Monthly Archives: January 2008

One way to extract city state country using SQL

Consider a table in which you have addresses of the following form:

  • 123 Main St, City, State, Country
  • 123 Main St, Apt 1, City, State, Country
  • 123 Main St, Apt 1, City, State 12345, Country
  • 123 Main St, Apt 1, City, State 12345
  • 123 Main St, Apt1, City, State 12345-1234

Say you want to extract the city portion of the data.

Following query does the trick for first three cases:

select DISTINCT REVERSE (SUBSTR(REVERSE(address), 1, INSTR(REVERSE(address),’,’, 1, 3) – 2)) citystatecountry
from address_table

Query can be easily modified to handle other similar cases as well.

I fully recognize that this Query is just clever, not optimal. In fact, if you are using Oracle 10g, you may be able to use a neat regexp to achieve this result easily. Still, this may help out someone who like me was stuck on 9i.

On the flip side, if you know of an easier way (or if you have the regexp working in 10g), please leave me a  note.


My new Philips 1 GB MP3 player

I got me a late Christmas present – a 1 GB Philips MP3 player! This spiffy device is such a relief over my iPod. Phew. I can already sense a change in my mood.

So many plus points!

  • It was a breeze to setup – no software to install (though it comes with a CD). I simply connect it using my regular USB-mini USB cable, and presto, it appears as the regular E:. Copy my MP3s, and it is ready to go.
  • Runs on a simply AAA battery. Clearly, someone ought to tell Steve Jobs that if you are designing a portable music player, give the user the option of being truly portable.
  • No iTunes! (no more comments)
  • No weird format – plays MP3, wav, whatever you have. No need to convert (either music or religion)
  • Much much smaller than an iPod, and the small visual screen shows the number playing just perfectly.
  • You can use this as a voice recorder!
  • Costs $42.98 from Walmart, as opposed to $149.99.
  • Standards based:
    • The same cable that I can use to connect to my blackberry, the bluetooth, camera, GPS can be used to connect to this Philips player. No reason to get a special iPod cable.
    • Plays all standard music formats

Enterprise Applications

There are certain variables that are available by default in JSP. Such as: request, response, out, session. All of these are not independent variables. For example, “out” can be derived from “response”. Still, they are all available as it is assumed that a JSP developer would very likely require them, so they are there for convenience.

A JSP developer who wrote code such as “PrintWriter myout = response.getWriter()” may be considered as a neophyte (special cases excluding).

When we, as developers write an “enterprise” application, we write large pieces of code to reach a common objective. We frequently use the word Enterprise Java to underline the fact that “we are not just plain java developers, we are enterprise grade”. We are saying that we write such large pieces of code, that we are effectively writing our own frameworks.

During his morning’s code review I noticed code very similar to the following:

// Defined in the include.jsp
String userType = (String) (session.getAttribute(“USERTYPE”));
// Code from a different JSP, which includes include.jsp
String userTypeStr = (String) (session.getAttribute(“USERTYPE”));

Most interestingly, both the pieces of code were written by the same developer! Apparently, the guy forgot that a convenient variable is already defined above! That is not such a terrible thing after all. The question really is, how to standardize and document these infrastructure variables? The IDEs can definitely help in this matter (Auto suggest, etc), but at the core, it is a design, training and education exercise.