Thought Delimited

In addition to the normal aggregate functions (Count, Sum, Avg, etc.), most database implementations of SQL also include functions for manipulating string values.  One of those is substr(), which allows you to extract a substring of the text value in a database field by specifying the start position and the number of characters. 

So if you wanted to extract the first 5 digits of a 9-digit postal code from your "zipCode" field.  You could do it like so:

select substr(zipCode,1,5) as mainZip...

...where 1 is the starting position and 5 the number of characters.  If you knew that the format of all the zip codes was "xxxxx-xxxx", then you could also retrieve the last 4-digits by moving the starting position and adjusting the character count:

select substring(zipCode,6,4) as extendedZip...

But what if you had strings where you wanted to extract the last 4 letters in the string, but the string length varied, like these values:

Professor - GVPT
Assist. Professor - LTSC
Prof. Emeritus - ENGR

You could use the length() function to get the full length of the string and then subtract one less than the number of characters you want from it to get the starting position:

select substr(nameDept,length(nameDept)-3,4) as dept...

...but Oracle and MySQL provide a simpler method:  you can designate the starting position from the right end of the string by using a negative number:

select substr(nameDept,-4,4) as dept...

I'm currently working on an application that involves long-term storage of assessment data. Users submit records of their activities and assess their performance, and then reviewers look over those assessments and denote whether they agree or disagree with them. Each assessment database record is related to a reviewer through the unique reviewer id that is part of the assessment record, and I can use that relationship to retrieve the reviewer's name whenever I display the assessment record.

It's a standard example of table normalization. If the reviewer's name was stored within the assessment record itself, and the reviewer changed their name for some reason (marriage, divorce, mid-life crisis, etc.), the application would have to update the name in both the reviewer's record AND the assessment record. But by using the reviewer's id in the assessment record to establish a relationship between the assessment record and the reviewer record, the reviewer's name only needs to be recorded or updated once.

However, this project will entail keeping the assessment data for an undetermined number of years. With the data arrangement I just described, that means I would have to store the assessment records and all of the related reviewer records if I want to be able to keep showing the name of the reviewer when looking at older assessment records. That could result in keeping a lot of extra data about reviewers (addresses, e-mail addresses, logins, passwords, etc.) who are no longer associated with the program simply because we need to keep their name tied to the assessments.

I think this is one of those situations where it makes sense to repeat a little data. Recording the reviewer's name in the assessment records allows me to let the administrative users of the application delete reviewer user accounts without impacting historical data. It means a bit more work in keeping the reviewer's name the same in both records, but in the long run I think it's worth the effort.

I was trying out the ColdFusion 8 <cfdbinfo> tag for the first time today, attempting to retrieve the names of all of the tables in a particular Oracle datasource. I used a <cfdump> to return the results, and I was startled to see a query object containing over 2,000 records.

A bit more than the 35 records I was expecting.

All of the unexpected entries were system tables or views. I have no idea what purpose they serve or if they're even part of the datasource per se. It's possible it's an anomaly resulting from the way our DBAs have our Oracle server configured, rather than something that will affect anyone trying to use the <cfdbinfo> tag against an Oracle database, but I thought it worth sharing.

Fortunately, I should be able to use the "pattern" attribute of <cfdbinfo> to retrieve just the tables I need for my purpose.