4

Need to add leading zeros to column values(varchar) and update it in same table. ex: 431 as 000431, 5431 as 005431, 64531 as 064531, basically i need to substitute zeros to the data so that it is 6 digits. this is in sybase ase.

thanks

max
  • 59
  • 1
  • 2
  • 8
  • 1
    any better than SELECT RIGHT('000000'+ CONVERT(VARCHAR,actuals),6) FROM nbr_actuals – max Mar 23 '16 at 21:15

2 Answers2

3

If you can't use LPAD(), and the source number is an integer, this solution is maybe a little nicer than yours:

RIGHT(1000000+Number, 6)

But, as yours, it produces unexpected results if the value is negative or has more than 6 digits.

maf-soft
  • 2,335
  • 3
  • 26
  • 49
1

In case you're running ASE 16.0SP01 or later, you can use the built-in function LPAD(). Otherwise, the expression you quoted is the way to go (which you could wrap into a SQL function to make it easier to use)

RobV
  • 2,263
  • 1
  • 11
  • 7
  • 1
    Thank you for the response, I am working on ASE 12 :) I went with the same expression. – max Mar 28 '16 at 16:35