Developer

A few SQL queries

by

I had to do some basic analysis of email addresses at my company the other day.  Here are a few useful SQL queries.

Show 10 records with the most data in field ’email’ (10 longest email addresses)

SELECT TOP 10 email, LEN(email) AS fieldLength
FROM person
ORDER BY fieldLength DESC

Count all records with field ’email’ over 20 chars

SELECT COUNT(*) AS emailOver20
FROM person
WHERE LEN(email) > 20

Display the average length of data (in characters) of field ’email’

SELECT AVG(LEN(email)) AS emailAvgLength
FROM person

Prepend existing data in SQL

by

Disclaimer: This tip will probably be really basic for most SQL folks.

The task: The company I work for has a web application that does some basic tracking of grants. The system feeding information to this application just had an across the board change to the numbering scheme of the grants. All grants must have a prefix of “999-“. Therefore, any grant not beginning with “999-” must be updated. Example: a grant with the current number 8789966 needs to be 999-8789966.

The solution: The SQL below does two things. It updates all the grants to prepend the 999- prefix while also skipping any grants that are already correctly prefixed.


UPDATE tbl_grants
SET grant_no = '999-' + grant_no
WHERE grant_no NOT LIKE '999-%'

Subversion on Ubuntu (Feisty) with a Mac Client

by

Subversion gets my ducks in a row

As part of a continuous effort to improve my organization (a.k.a. not lose stuff) I have finally set up Subversion at home. If you are not familiar with Subversion it is an open source version control system used (mostly) by developers to keep up with changes to their codebase.

I’ve spent most of my career as kind of a “one man team” where I have been responsible for all phases of development and maintenance of code. However, I was exposed to Subversion when I contracted briefly last year in a multi-developer environment. I made a mental note at that time to revisit Subversion at a later date.

At last that date has come…

Subversion is now running at home on an old Gateway pc that I recently “upgraded” from XP to Ubuntu. I’m a total newbie with Linux, but I’ll be posting some on it in the future. Thanks to this tutorial the process was ridiculously easy (less than 5 minutes).

The next step was to get an SVN client. My previous exposure to Subversion was in the Windows world so we used TortoiseSVN. I have a PowerBook and an iMac at home though so I hunted down scplugin which integrates with Mac Finder the way TortoiseSVN integrates with Windows Explorer.

Now I just have to get all my code checked in.

Comment yer code with myRev Dreamweaver Extension

by

myRev logo

It’s March and amazingly I have stuck to one of my New Year’s Resolutions for work. What is that resolution you may or may not be asking? …Comment my frigging code.

Several years ago I fooled around with making a few little Dreamweaver Extensions. I pulled out an old one, dusted if off, pimped it up, and am providing it here for your benefit. It’s called myRev and it will insert a comment header for you (ideally you would insert it at the top of the page you are developing). Here is a sample of the output:


<!---
============================================================
File: SomeFile.cfm
Author: Christopher C. Simmons (CCS)
Date: 3.4.2008
Purpose: To perform some calculations
History: 0.1 Initial Release
============================================================
--->

You can use it with Coldfusion, PHP, CSS, JavaScript, or HTML. The header knows what comment style to use based on your choice in the Extension’s UI.

Here’s the fine print…

csimmons.net, LLC supplies this software AS IS and makes no guarantees for your use of it. csimmons.net, LLC is not responsible for any damage or pain the use of this product may cause you.

GET IT HERE (or by clicking the myRev icon above). To install it just unzip it and double click the myRev.mxp file. The Dreamweaver Extension manager will then install it.