convert the numbers

Question

First I am converting the value from the table as integer like

cast(convert(int, isnull(b.temp,0)) as varchar(500))

and then I would like to output values as written below for example

1 as 001
12 as 012
123 as 123

-1 as -001 
-15 as -015
-234 as -234

and if length of the integer value is more than 3 then do not display any value or remove it. If integer has Minus(-) sign then it is not part of the length so -001 is consider as length 3

so 001 and -001 is acceptable as length 3

How can I do that?

Exact duplicate. http://stackoverflow.com/questions/5540064/how-to-make-padleft-in-sql-server-2008-query — Bert, Jul 20 '12 at 19:36
This solution that you mentioned is good for positive values but not good for negative value — user680865, Jul 20 '12 at 19:42
I don't think this is an *exact* duplicate, but the answer from Bert's link solves this problem. Pad the absolute value as it shows, then stick a negative identifier in front if needed. — Esoteric Screen Name, Jul 20 '12 at 19:49

score 2 · Answer 1 · answered Jul 20 '12 at 19:43

2

It looks like you are trying to 0 pad numbers in a range. This is one way I do it:

select (case when val between 0 and 999
             then right('000'+cast(<col> as varchar(100)), 3)
             when val between -999 and 0
             then '-'+right('000'+cast(abs(<col>) as varchar(100)), 3)
             else ''
        end)
from t

answered Jul 20 '12 at 19:43

Gordon Linoff

1,242,037
58
646
786

You are right. It occurs to me that I almost never work with negative numbers that need to be zero padded. In any case, I was already fixing that problem. – Gordon Linoff Jul 20 '12 at 19:46

user680865 · Answer 2 · 2012-07-20T20:12:13.300

0

Solution below works. Thanks

declare @num1 as varchar(50)
set @num1 = '1'
       select (case 
               when @num1 between 0 and 999  then right('000'+cast(@num1 as varchar(100)), 3) 
               when @num1 between -999 and 0  then '-'+right('000'+cast(abs(@num1) as varchar(100)), 3)  
               else ''         
               end) as t

edited Jul 20 '12 at 20:12

answered Jul 20 '12 at 20:02

user680865

289
2
6
13

score 0 · Accepted Answer · answered Jul 20 '12 at 20:11

There really is no efficient way to do this, but I understand there are situations where this needs to be done. I would probably go about it with something like this turned into a function.

DECLARE @myInt as INT
    SET @myInt = -9

DECLARE @padding AS VARCHAR(1)
    SET @padding = '0'
DECLARE @length AS INT
    SET @length = 3 - LEN(ABS(@myInt))

DECLARE @result AS VARCHAR(5)
    SET @result = REPLICATE(@padding, @length) + cast(ABS(@myInt) as VARCHAR(3))
IF @myInt < 0   
        SET @result = '-' + @result

SELECT @result

This really should be done in code and not sql.

convert the numbers

3 Answers3