Special integer conversion

Question

Is it possible to convert an integer in 4 position varchar.. Example I have the integer 1 and I get varchar '0001', for integer 550 I get varchar '0550' .

Thanks

What should happen to integer `99999`? or integer `-9999`? Or are we guaranteed between `0-9999`? — Martin Smith, Sep 16 '11 at 11:52
Have a look at [this question](http://stackoverflow.com/questions/121864/most-efficient-t-sql-way-to-pad-a-varchar-on-the-left-to-a-certain-length), not an exact duplicate but could help. — Jacob, Sep 16 '11 at 11:53
[This question](http://stackoverflow.com/questions/2397161/how-to-convert-int-to-char-with-leading-zeros) also could help you. — denolk, Sep 16 '11 at 11:54

Hugh Jones · Accepted Answer · 2011-09-16T14:57:47.667

or this ?

right('000'+ convert(varchar,MyNum),4)

I just did some rough timings on the various methods and this solution seemed to be a little quicker than the others. That surprised me ...

DECLARE @loop INT;
DECLARE @MyNum INT;
DECLARE @Upper INT;
DECLARE @Lower INT;
DECLARE @result VARCHAR;
DECLARE @start DATETIME;

SET @Lower = 1;
SET @Upper = 9999;  
SET @loop = 10000;

SET @start = GETDATE();

WHILE @loop > 0 BEGIN
  SELECT @MyNum = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0);
  SET @loop = @loop -1;
  SET @result = right('000'+ convert(varchar,@MyNum),4);
  -- SET @result = right(10000 + @MyNum, 4);
  -- SET @result = right(convert(float, @MyNum) / 10000, 4);
  -- SET @result = stuff('0000', 1 + 4 - len(@MyNum), len(@MyNum), @MyNum);
  -- SET @result = replace(str(@MyNum, 4), ' ', '0');   
END;

SELECT GETDATE() - @start;

I wonder if this might be quicker ...select right(convert(float, MyNum) / 10000, 4) — Hugh Jones, Sep 16 '11 at 12:58
IF it is better then Mikael's answer below is probably better still. — Hugh Jones, Sep 16 '11 at 13:09

score 1 · Answer 2 · answered Sep 16 '11 at 11:54

1

Try this :

SELECT REPLACE(STR(550, 4), ' ', '0')

!

answered Sep 16 '11 at 11:54

Arnaud F.

8,252
11
53
102

score 1 · Answer 3 · answered Sep 16 '11 at 12:43

1

declare @Num int 
set @Num = 1

select right(10000+@Num, 4)

answered Sep 16 '11 at 12:43

Mikael Eriksson

136,425
22
210
281

score 0 · Answer 4 · answered Sep 16 '11 at 11:59

Onw way (NULL if len input > 4);

;with t(f) as (
    select 1 union select 11 union select 111 union select 1111
)

select
    f,
    stuff('0000', 1 + 4 - len(f), len(f), f)
from t

>>
f      (No column name)
1      0001
11     0011
11     0111
1111   1111

Special integer conversion

4 Answers4