Stupid Oracle SQL Tricks, part 1

One thing I've found in the new job is my SQL skills have been called upon with a vengeance. One thing that has taken me by surprise (but really shouldn't have) is that I'm not allowed to modify most of the databases I use. In the past I've usually had access to create a view or modify a schema if it wasn't working well. Not anymore. So, I've taken to writing a lot of crazy SQL to normalize data that otherwise isn't pretty. Sometimes, this takes the simple form of something like

INITCAP(table.name_item) Formatted_Name

or

CASE WHEN table.checkbox_item = 'Y' THEN 'YES' ELSE 'NO' END Verify_YN


but I've also had to do more complicated things like concatenate a code and description:

table.code_item || ' - ' || INITCAP(table.desc_item) Full_Desc


or make sure a field is returning a number instead of a numeric string (note that this example only covers positive numbers):

TO_NUMBER(regexp_substr(table.num_item,'^[[:digit:]]*\.?[[:digit:]]*$'), '999D99') Numeric_Item


or create groupings of records by quarter (and display the quarter as 2009-Q1 or whatever):

to_char(table.date_item, 'YYYY') || '-Q' || to_char(table.date_item, 'Q') Date_by_Quarter


Necessity is the mother of invention, no?