SQL tricks – creative JOINs with legacy data

Have you ever needed to extract data from old tables and use it in a way the table designer never planned on? This seems to be an all-too-common experience.

Recently, I learned that I could parse a number out of a string field, cast it as an integer, and actually use that result in a JOIN (as if it were a foreign key) to another table's integer primary key. Here is the JOIN part of my query:


...
inner join listings as list
on list.listing_id =
CAST ( RIGHT(op.description,(LEN(op.description)-CHARINDEX(':',op.description))) AS integer )


A few years ago, before I began to understand the power of SQL, I would have done something like this by hand in Excel. Details of the problem and solution are included below if you'd like to read on …