T-SQL is not the best language in the world for formatting strings. It can be done, but typically you are going to end up with lots of function calls. Let’s say I want to output the Rate as a string 5 characters wide padded with 0’s on the left. So 4.25 becomes 00425. Here is one way to do this. It may not be the best way but it’s perfect for this demonstration.

DECLARE @Rate decimal(10,2)
SET @Rate = 4.25
SELECT RIGHT('00000' + 
REPLACE(CAST(CAST(ROUND(@Rate, 0) AS INT)) AS VARCHAR(20)),'.',''),5)

Now depending on how much expereince you have with things like this you may or may not have noticed I have a bug in the code. (Yes deliberatly.) So how do we find it?

First here’s the error

Msg 1035, Level 15, State 10, Line 3
Incorrect syntax near 'CAST', expected 'AS'.

Well I have two CASTs and two AS clauses so why the error? Try laying it out like this:

DECLARE @Rate decimal(10,2)
SET @Rate = 4.25
SELECT
RIGHT('00000' + 
REPLACE(
CAST(
CAST(
ROUND(
@Rate
, 0)
AS INT)
 ) -- <-
AS VARCHAR(20))
,'.','')
,5)

Well that’s a bit easier to see. There’s an extra ). So let’s remove that.

DECLARE @Rate decimal(10,2)
SET @Rate = 4.25
SELECT
RIGHT('00000' + 
REPLACE(
CAST(
CAST(
ROUND(
@Rate
, 0)
AS INT)
AS VARCHAR(20))
,'.','')
,5)

Well at least that doesn’t return an error. It does however return 00005 which is not exactly what we want. But with this new format we can easily test out some options. I think it’s the ROUND() that’s the problem so let’s comment that out.

DECLARE @Rate decimal(10,2)
SET @Rate = 4.25
SELECT
RIGHT('00000' + 
REPLACE(
CAST(
CAST(
--ROUND(
@Rate
--, 0)
AS INT)
AS VARCHAR(20))
,'.','')
,5)

No change. Let’s try getting rid of the CAST AS INT as well.

DECLARE @Rate decimal(10,2)
SET @Rate = 4.25
SELECT
RIGHT('00000' + 
REPLACE(
CAST(
--CAST(
--ROUND(
@Rate
--, 0)
--AS INT)
AS VARCHAR(20))
,'.','')
,5)

And that’s what we want! Now it’s no big deal to delete the four commented out lines and condense the whole thing back down.

DECLARE @Rate decimal(10,2)
SET @Rate = 4.25
SELECT RIGHT('00000' + REPLACE(CAST(@Rate AS VARCHAR(20)),'.',''),5)

Generally I recommend leaving it condensed down like this when in production (or just not being worked on) and then break it out when you need to figure out a problem or modify something.

Hope this helps!

Filed under: Microsoft SQL Server, Problem Resolution, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, problem resolution, T-SQL