2

I have to export a table in fixed format way. When I have numeric field, I have to add leading zeroes.

I know with SQL Server 2012, I can use FORMAT(). How to do in SQL Server 2005?

For instance, in a column with number 18, with fixed length 10, I have to export: 

0000000018
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
stighy
  • 7,260
  • 25
  • 97
  • 157
  • Possible duplicate of [Pad a string with leading zeros so it's 3 characters long in SQL Server 2008](http://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008) – HABO Jul 06 '16 at 15:32

4 Answers4

4

You have to convert it to a varchar and use right.

select right(replicate('0', 10) + convert(varchar(10), YourNumberColumn), 10)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • Hi Sean, I dislike this approach, 'cause if the input number is wider than your number (input = 1234 but your target width = 3 leads to a wrong result). Please have a look at my answer... – Shnugo Jul 06 '16 at 14:41
4

I prefer the syntax

SELECT REPLACE(STR(YourNumber,X),' ','0')

The X is the count of digits you want.

The advantage: Other approaches would cut the number and lead to wrong results, if the input is wider than the target length. This approach would create a chain of asterisc ("***")

Try this

DECLARE @tbl TABLE(Nmbr INT)
INSERT INTO @tbl VALUES(1),(12),(123),(1234);

SELECT REPLACE(STR(Nmbr,3),' ','0')
FROM @tbl

The result:

001
012
123
***
Shnugo
  • 66,100
  • 9
  • 53
  • 114
0
right('00000000' + cast(YourValue as varchar(10)), 10)
xdd
  • 535
  • 2
  • 4
  • You are missing a parentheses and this results in the inputted value (YourValue). – S3S Jul 06 '16 at 13:50
  • The approach with `RIGHT` will cut a number if your input is wider than the target width... You might have a look at my answer... – Shnugo Jul 06 '16 at 14:42
0

You can make like this 

replicate('0', 10 - Floor(LOG10(myField) + 1) + myField
Y. M.
  • 107
  • 9
  • 1
    What is the point of using LOG10 here? I am not saying it is incorrect, I want to understand the logic of why that is there. – Sean Lange Jul 06 '16 at 14:50
  • Did you try this? I think you should cast the `myField` to string, otherwise you'll have numeric addition - which is not what you want. And there seems to be a flaw with numbers `<=10`... – Shnugo Jul 06 '16 at 14:51
  • The use of LOG10 it´s a way I like to use to fine how many digit por example LOG10(10) its 1 and LOG10(99) it´s les than 2 and LOG(100) is 2 – Y. M. Jul 06 '16 at 15:47