Odd things found when working with data

Sometimes, you’ll find all sorts of craziness when you’re working with data. Especially if you don’t expect there to be any craziness. Let me show you what I mean by a story of what I had happen just this week.

In mapping some data from one database to another, I was asked to do the following conversion:

SELECT CASE
           WHEN FieldName IS NULL OR FieldName = '' 
                THEN 'Insert Garbage Data Here'
           ELSE NULL
END as WonderfulTranslatedField 

The query ran just fine so I happily completed my work for the day. I sent it to my QA team and had them check it out before pushing it to Production. They sent it back with a note saying “We expect a lot more “Insert Garbage Data Here” to show.

Being a good data developer – or someone who just wants to show QA up (take your pick) – decided to dig deep into what FieldName really shows. I found a lot of the following:

My first thought was, “Sherpa, there must be more than just a blank string in FieldName. Let’s use the TRIM function on it and we’ll be Golden™.” So off I go using the TRIM function to get back the data I expect. Before sending it off to QA and closing the ticket, I review the data.

Sigh. No change to our data.

I said to myself “Sherpa, somebody must have hit Enter when they were putting the data in there. Let’s remove that from the field, keep it TRIMed, and we should be Golden™!”

All right. This is just being mean to us. Let’s see what this data has in the first character if anything?

So I went above and beyond showing the first 3 characters because it’s a simple query. For Char1 – the first character – I use the LEFT function to grab the first character and then the ASCII function to see what the ASCII code for that is. I do the same thing for the 2nd and 3rd characters to see what value they have, just using the SUBSTRING function.

When I look up the ASCII value of 0 in my handy dandy notebook, I find that it means NULL. So, I have a NULL character value without it being NULL? Fun time with the Sherpa!

Now that I know what the data actually shows, let’s change our query to get some good data from it, shall we?

SELECT CASE
           WHEN FieldName IS NULL 
                OR REPLACE(FieldName, char(0), '') = '' 
                THEN 'Insert Garbage Data Here'
           ELSE NULL
END as WonderfulTranslatedField 

Boom-shack-a-lacka, my friends! Boom-shack-a-lacka.

Today’s word of warning when working with data: Try things that you’ve seen before to correct the issue you’re having, then go far outside what you’ve seen before to see what you may have missed. Oh, and thank your QA person for catching things you didn’t catch the first time around.