As some may know, the SQL COALESCE statement will replace any nulls it finds with some other value. This function is a rock and staple of coding in SQL. It is similar to the ISNULL SQL function except it returns the non-null value instead of a boolean without having to write an “if” statement, and is extremely useful in “SELECT” statements.
Sometime back, I was at a job interview and the guy took out a sheet of SQL code and handed it to me. He had three lines marked in a nasty multi-line SQL statement, and said, “What are these lines doing?” or something to that effect. I totally froze. I acted like I’d never seen a lick of SQL code before. But, I must say, that he did have some code that I hadn’t seen quite like this before.
Although I’ve used the SQL COALESCE statement before, I’ve never really used it like this guy was using it.
I have always used something like the below thousands of times:
SELECT name, address, state, zip, COALESCE (phone,'N/A') FROM table
But this guy had something like:
My problem in this interview was that I obviously hadn’t finish reading the SQL book on COALESCE, way back when. What I found that day was that instead of COALESCE only working on two values, like I’ve always done, (i.e. COALESCE(value1,value2)) I found out that day that it will keep going down a list until it finds any non-null value. Only if it never finds a non-null value will it return a null.
My mind was still on that COALESCE statement while I was trying to answer the other two lines, which I did not do well on either. For one, I forgot that CHARINDEX was one based instead of zero based. Heck! I at first, didn’t even recognize the CHARINDEX as a SQL expression. This isn’t the normal me! I can’t even remember the other line I had to decode. Man! It was like I entered into LA-LA land.
I’m sure as he was watching me stumble around, he considered my years of experience as old wives tales. The code “ID-10-T” comes to mind as something he might have uttered silently in his mind.
As soon as I got home, I whipped out my “trusty” ole “SQL Server Management Console” and created a Query on my “Northwind” database. Sure nuff, it worked just like that guy’s code was coded.
And the egg on my face, dripped from my nose and cheek down to the toes of my shoes…
Yep, I learned a good lesson that day, but it didn’t go well with the crow I ate along with it.
No…I didn’t get the job, but…they were nice to me as I left their building.
I’m sure glad that the door didn’t hit my “Bee-Hind” as I was leaving…