Let's say I have a column that is a varchar(3).

What is the best way to pad result with 0's where length is < 3.

Column        Result
-----------        ---------
1                    001
12                  012
123                123

I hope this makes sense.

Thanks,

Yosh

Try,

select c1, right('000' + c1, 3) as c2
from t1


AMB

Show quoteHide quote

One way: RIGHT('000' + CONVERT(VARCHAR, col), 3)

Let's say I have a column that is a varchar(3).

What is the best way to pad result with 0's where length is < 3.

Column        Result
-----------        ---------
1                    001
12                  012
123                123

I hope this makes sense.

Thanks,

Yosh

This is one way of doing it.  Replace @n & @x with your field names.

declare @n varchar(10)
declare @x int

set @n='000'
set @x=12

select substring(@n,1, len(cast(@x as varchar(10)))-1) + cast(@x as varchar(10))

  Let's say I have a column that is a varchar(3).

  What is the best way to pad result with 0's where length is < 3.

  Column        Result
  -----------        ---------
  1                    001
  12                  012
  123                123

  I hope this makes sense.

  Thanks,

  Yosh

Nice!

Thanks Everyone!

  This is one way of doing it.  Replace @n & @x with your field names.

  declare @n varchar(10)
  declare @x int

  set @n='000'
  set @x=12

  select substring(@n,1, len(cast(@x as varchar(10)))-1) + cast(@x as varchar(10))

    Let's say I have a column that is a varchar(3).

    What is the best way to pad result with 0's where length is < 3.

    Column        Result
    -----------        ---------
    1                    001
    12                  012
    123                123

    I hope this makes sense.

    Thanks,

    Yosh

[OT] SQL Humor
composite key structure
Default Column Values
Updating Tables with Computed Columns - SET ARITHABORT
Summing muliple variable columns