You can tell a lot about my state of mind by the books I’m reading. Lately, it’s Urban Fantasy with a Noir feel to it. Specifically, I’m reading Mike Carey’s Felix Castor series, and I just finished a book by Richard Kadrey called Sandman Slim: A Novel. I love the anti-hero. The protagonist who is gritty and dirty and has a few great scars is my kind of guy. He unapologetically breaks the rules and isn’t all, “it’s more about the journey than the destination.” For him, destination is what matters, no matter now you got there.
Lately, I feel a bit like the scarred anti-hero. I’m doing some things in a production environment that I’m not totally thrilled about, and I wish I could stop the line and do things the “right” way. I want to use SSIS to transform data. I want to encapsulate processes into neat, repeatable, parameterized modules. But, you know what? When there’s a same-day turnaround on a request, you make do. You go a little Noir on your T-SQL, know what I mean?
I want to show you two things that I’ve actually done in the past few weeks. No, given a nice, neat environment, this SQL might never have been written. Am I proud of it? Well, yes. Yes I am. At the end of the day, I got the customer what he needed. Was it pretty? No. I’m cool with that. Being the anti-hero is kind of fun every once in a while.
I needed to give a guy a text file in fixed-width format. I had a process from my predecessor that just wasn’t working. The file was already late. So here’s what I did. I’m using the AdventureWorks database to show an example.
SELECT LEFT((ISNULL(Title,'')+SPACE(50)), 8)+ LEFT((ISNULL(FirstName,'')+SPACE(100)), 20)+ LEFT((ISNULL(LastName,'')+SPACE(100)), 30)+ LEFT((ISNULL(MiddleName,'')+SPACE(100)), 5)+ LEFT((ISNULL(EmailAddress,'')+SPACE(100)), 35)+ LEFT((ISNULL(Phone,'')+SPACE(100)), 25) FROM AdventureWorks.Person.Contact ;
Paste it into Notepad and see how it looks:
I save the text file and send it on. Pour myself a whiskey, neat, and light up an unfiltered Lucky Strike. Okay, not really, but you know what I mean.
A quick run-down:
ISNULL: If any of the values I’m concatenating are NULL, then the entire string will come back as NULL. I wrap all of my columns in ISNULL like so:
This sets the value to an empty string if the value is NULL.
SPACE: This handy little string function will pad the given number of spaces onto the result you return. I want to make sure I end up with enough padded spaces to fill out the fixed-width portion of that column. So, I pad the output:
This will give me the output from the Title column, plus 50 spaces.
LEFT: Now, not every value coming out of the database is going to have the exact same number of columns. So, I use the LEFT function to trim it down to the exact length I want. LEFT will take the left-most number of characters you tell it to. If I say,
LEFT((ISNULL(Title,”)+SPACE(50)), 8 )
I’m telling it to give me characters 1-8 that are returned. Since I’ve padded my output with spaces, it’ll be the result from the column, plus as many spaces as I need to pad the output to 8.
Pretty? No. Functional? Yes. Noir SQL? Absolutely.
Remove Unwanted Characters
Next up, I have a source file I use from another department. It comes in Excel format, and includes a phone number. I’m supposed to get something that looks like this: 1112223333. Nice, neat, simple. What do I get? A hodge-podge of phone number formats. I’m looking at something like this:
CREATE TABLE PhoneNumber ( PhoneNumber varchar(50) ); INSERT INTO PhoneNumber(PhoneNumber) VALUES ('1112223333'), ('(111) 222-3333'), ('111-222-3333'), ('111 222 3333'); SELECT PhoneNumber FROM PhoneNumber
Okay. So I need to clean these numbers up quickly. Destination, not journey, my friends. I’m the anti-hero. I import the data into SQL Server using the Import/Export utility so I can manipulate the data. Then, I run this nifty little REPLACE statement:
SELECT PhoneNumber, CASE WHEN ISNUMERIC(PhoneNumber) = 0 THEN REPLACE( REPLACE( REPLACE( REPLACE(PhoneNumber, '-', ''), --Strip out dashes ' ', ''), --Strip out spaces ')', ''), --Strip out close parenthesis '(', '') --Strip out open parenthesis ELSE PhoneNumber END as FormattedPhoneNumber FROM dbo.PhoneNumber
Check out the results:
Sweet. It’s quick, it’s dirty, and it saved me having to wait on the source data provider to clean things up on his end. I turn the query into an UPDATE statement, and I’ve got clean data to import. Again, a run-down of the functions:
ISNUMERIC: Tells me whether the value I’m passing is qualifies as a number or not. NOTE: It recognizes hexadecimal as a number, so use carefully. I set up a CASE statement that asks if the value is numeric. If it is, that means I don’t have any characters like “(“, “)”, or “-“ in there. If not, I apply a nested REPLACE to the value.
REPLACE: Replace is awesome. I can say something like this: REPLACE(PhoneNumber, ‘-‘, ‘’). This is saying that if I find a dash, I want to replace it with an empty string. What’s really cool is that I can nest them. So, I can tell it to remove the dashes, then the spaces, then the open parenthesis, and finally the close parenthesis in one statement.
Bottom line: Sometimes things just have to get done. The difference between an anti-hero and a true antagonist is that we anti-heroes know to go back and do things the right way as soon as we get a moment to breathe. In the meantime, don’t apologize for leaving behind a few unmarked graves when you need to get the job done. We’re anti-heroes. We have the scars to prove it.