Digits - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Digits

Author

Topic

niranjankumark
Posting Yak Master

164 Posts

Posted - 2009-04-26 : 08:16:15

In db2 have function as DIGITS Which does .....

Ex: col1 - Int(4)

data is stored like
1
12
123

select DIGITS(COL1) -- OUTPUT WILL BE
0001
0012
0123
IS THERE ANY FUNTION IN sql sever ???

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-26 : 08:43:01

SELECT REPLACE(STR(Col1), 4, 0), ' ', '0') FROM myTable

The STR() function can format a number with a total length and number of decimal places, padded on the left with spaces. The REPLACE() function replaces those spaces with zeros.

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-27 : 08:07:20

You can get the desired result by using given query,
I feel that there is no separate function for that.

SELECT RIGHT(REPLICATE('0',3) + CAST(Col AS VARCHAR(5)),4)
FROM MyTable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-27 : 08:45:29

quote:
Originally posted by robvolk

SELECT REPLACE(STR(Col1), 4, 0), ' ', '0') FROM myTable

The STR() function can format a number with a total length and number of decimal places, padded on the left with spaces. The REPLACE() function replaces those spaces with zeros.

You had extra closing paranthesis in Col1

It should be

SELECT REPLACE(STR(Col1, 4, 0), ' ', '0') FROM myTable

Madhivanan

Failing to plan is Planning to fail

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-27 : 08:47:29

Thanks for fixing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources