Tuesday, August 24, 2010

There's a zillion posts and articles about how to escape single quotes in T-SQL, but what I wanted to do was strip out the characters that were inside the single quotes. After a ridiculous amount of Googling turned up nothing, I spent an even more ridiculous amount of time figuring it out.

The code below is a complete, working example of removing the characters from between two single quotes for every row of a table. It also removes the single quotes; modifying the code to leave the single quotes in is left as an exercise for the reader. (Ok, just add 1 to PosBeg and subtract 1 from PosEnd.)

The really important thing I learned from this is that (a) when people say T-SQL was not intended for string manipulation, they're not kidding, and (b) the way to do anything remotely fancy with strings is one tiny step at a time. As an old C/C++/C# developer, I underestimated the difficulty of this problem: playing with strings ceased to be interesting as soon as CString came out. I finally got it through my head that the only hope of implementing this in an intelligible way was to start from the inside, and work my way out. That's where the intermediate variables PosBeg, PosEnd, and Length came from - they were the baby steps I took along the way.

I haven't programmed using intermediate variables in a long, long time. I don't write C# like that, and I sure don't write other T-SQL that way, but string manipulation in T-SQL drove me back to it. I wonder if it's T-SQL's lack of powerful string operators, or just the familiarity of old habits, that are to blame?

Wednesday, August 18, 2010

If you're like me, it's a lot easier to understand things with a picture. With that in mind, here's a script you can run as a Job which will print out a visual representation of the Jobs that ran in the last 24 hours. The results are emailed to whatever address you provide (near the end of the script).

Before making the move to SQL Server, Larry specialized in Windows programming in C/Win32, C++/MFC, and C#/.NET. His blog focuses on query tuning, database optimization, and system performance, and features handy scripts, practical tips, and occasional dispatches from the many dark corners of SQL Server.