Format data in tables - 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 2000 Forums

SQL Server Development (2000)

Format data in tables

Author

Topic

rushdib
Yak Posting Veteran

93 Posts

Posted - 2003-11-12 : 09:32:04

Hi,
I have a column name called company code (int). Everytime the data is stored in the table I would like to store it as 4 digits.
Ex. 61 , should be 0061.
This can be done at the front end application, but is there a way to do this at the table level?

Thanks,

Rushdi

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-12 : 09:45:19

No,

It is a presentation layer thing...

Also an int can be a number up to...

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

Brett

8-)

nic
Posting Yak Master

209 Posts

Posted - 2003-11-12 : 11:05:56

I agree with Brett, this should be handled on the presentation level.

You can format it quite easily though:
RIGHT( '0000' + CAST(code AS VARCHAR),4)

Nic

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-12 : 11:44:32

Why should this be handled in the presentation layer???

This is like saying let the presentation layer convert a 2 digit state code into the state name instead of storing both the state code and state name in a state table.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-11-12 : 11:48:43

There is one MS option avail that will perform this.

If you have access xp It is easy to add just open the table in design and enter 0000 in the Format field.

If you do not you could script in this.

sp_addextendedproperty N'MS_Format', N'0000'

See BOL on sp_addextendedproperty as you will need to ref your field and table.

Jim
Users <> Logic

nic
Posting Yak Master

209 Posts

Posted - 2003-11-12 : 12:04:44

>> Why should this be handled in the presentation layer???

It all comes down to what is the value used for?

Padding an integer with extra 000 is formatting the data. If the value is an int, the underlying data should be stored as an int (not padded with extra 000). If the value is not really an int, maybe it is a userID or something that happens to contain all numeric values but could also be alpha-numeric, then it should be stored as a varchar and having the extra padding is fine. It all comes down to what is the data used for? If it is an int then it should be stored as an int. (and formatted in the presentation layer if need be.) If the value is really just a varchar (such as a userID or something similar) then storing it as 0061 is fine(as a varchar).

At least this is how I would handle it.

Nic

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-12 : 12:33:39

The original question:

quote:

Everytime the data is stored in the table I would like to store it as 4 digits.

For an int, No. Unless someone can show me how...

As a char(4), Yes.

If it is an int, isn't 4 digits dangerous

quote:

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

And:

quote:

This is like saying let the presentation layer convert a 2 digit state code into the state name instead of storing both the state code and state name in a state table.

I disagree, it's not like saying that at all....

Brett

8-)
[/quote]

Brett

8-)

rushdib
Yak Posting Veteran

93 Posts

Posted - 2003-11-12 : 12:49:51

Ya, I can't use an int. It has be nvarchar. It looks like it's better if format at this at the front end and store this at the data base.

Rushdi

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-12 : 13:08:21

While I agree with Brett on the int datatype issue and nic makes a good point with regards to coding practices, My argument and my reading of the post was to store a descriptor of companycode in this manner:


create table companycode
( 	companycodeid smallint primary key,
	companycodedesc char(4))
go

create proc UPAddCompanyCode
	@companycodeid smallint
AS
set nocount on

	insert into companycode 
	select @companycodeid,RIGHT( '0000' + CAST(@companycodeid AS VARCHAR),4) 
go
exec UPAddCompanyCode 61
select * from companycode
go
drop table companycode
drop proc UPAddCompanyCode
go

In this situation there is less code and less processing of the data on the presentation layer which I find to be a good thing. The companyid can be used for relational data and companydesc can be returned to the presentation layer for display.

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-12 : 13:13:21

To each his own...but I think you'll have more trouble

'61' <> '0061'

And if requests are going to be made of the db through sprocs, you'll have to apply this rule everywhere in the backend...

I would keep it simple...if the front end passes me a '61', but really meant '0061', I'll just return an error and tell it record not found...

Brett

8-)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-12 : 13:18:56

quote:
Originally posted by X002548

'61' <> '0061'

Its all in how you handle the request.


declare @i int
declare @n int
set @i = '0061'
set @n = 61
select @i where @i = @n

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-12 : 13:37:53

[beating dead horse]

Since int aint an option, more likely:


declare @i char(4)
declare @n char(4)
set @i = '0061'
set @n = 61
select @i, @n
select @i, @n where @i = @n

[/beating dead horse]

Again, to each his own...Thanks for the discussion...

Brett

8-)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-11-12 : 14:01:18

We used to use 0001 instead of 1 but that was for sorts on a Alphanumaric field.

I do display my Die IDs as 001147 but that is for scanning .

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources