tarr94 (5/6/2016)
Two of you have chimed in with a preference for keeping this sort of logic out of SQL. Could you please clarify why you feel this way? In my example, the query results are being pulled into a Crystal Report, so the only other alternative is to keep this logic in formula fields in Crystal (which is what we've been doing up to this point, but we're trying to move away from keeping this kind of logic in Crystal formulas). I'll mark Jacob's answer as the solution since he chimed in first and his query seems to do the trick, but I'd be interested in any opinions on whether the logic should be stored in SQL or Crystal.
The big reason is "layer separation" and all of the good things that go with it. While there are certainly exceptions, it's normally a good idea to keep the Presentation Layer separate from the Data Layer. It's especially important for date/times and currency so that local formatting can take place on the client side of the house.
Shifting gears, I find leading zeroes to be even more annoying than leading spaces. Again, there are exceptions that require them but I'm not a fan even then.
--Jeff Moden
Change is inevitable... Change for the better is not.