CODEWITHSUNDEEP
sql-servert-sql
stighy
stighy

Reputation: 7170

How to add zero in front of numeric value in T-SQL 2005 or 2008?

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

Upvotes: 2

Views: 11497

Answers (4)

Y. M.
Y. M.

Reputation: 107

You can make like this 

replicate('0', 10 - Floor(LOG10(myField) + 1) + myField

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

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

select right(replicate('0', 10) + convert(varchar(10), YourNumberColumn), 10)

Upvotes: 4

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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
***

Upvotes: 4

xdd
xdd

Reputation: 545

right('00000000' + cast(YourValue as varchar(10)), 10)

Upvotes: 0

Related Questions