How to append 00 to account number

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

General SQL Server Forums

New to SQL Server Programming

How to append 00 to account number

Author

Topic

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-27 : 17:15:31

my query is in this sturcture

declare @number numeric(10,0)
set @number =(select distinct Number from tablename where conditions)
select @number

how to appent 00 while displaying number

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-08-27 : 17:18:22

unless u use varchar. It wont work

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-27 : 17:19:26

can i not append 2 zeros with numeric data type??

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-08-27 : 17:19:28

Do you mean before or after the @number.
Cos if its before, unless u use varchar. It wont work

After select @number + '00' (if its varchar)

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-27 : 17:20:53

i need to append before the number...i will change to varchar..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-27 : 17:33:00

Since this is for display purposes, do not change to varchar. Just append 00 from your application. You should not be doing this in T-SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-08-27 : 17:38:11

Questions:
1. What is the reason to append the 00 ?
2. And is at the start or end ?

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-27 : 20:29:43

In the requirement it is given the display should contain 00 before number that is the format used

the append should be at start

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-27 : 20:39:48

You cannot append '00' to a numeric, only a varchar or char, and that is why it should be done in the front end, but in t-sql you just need:

SELECT '00'+ CONVERT(varchar(20),yourNumber)
FROM
yourTable

jim

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-08-28 : 00:43:11

Another way to see it is that numeric and int data types are similar to numbers and numbers cant start with 00.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-28 : 02:21:48

quote:
Originally posted by Chinni

In the requirement it is given the display should contain 00 before number that is the format used

the append should be at start

Where do you want to show formatted numbers?

Madhivanan

Failing to plan is Planning to fail

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-28 : 14:20:40

i would like to display in the result where the result reside in a table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-28 : 14:24:22

Our point is that you shouldn't be doing this in the database. Your application should be appending the 00 at display time. This is a presentation issue, not a database issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources