Data Type 4

[MarkZK]

Hi all,

This is probably a simple unimportant question, but can anyone tell me what the best data type to use is for 4 digits ?.

I know it'll always be four characters long and range from 0000 to 9999, I'm currently using varchar(4) and get the feeling I should be using something else.... as I say, not really important, I'd just like to get it right.

Thanks

 

[markros]

If you need 4 '0', then I would say use char(4). Otherwise you may check smallint type.

 

[MarkZK]

Hi Markros, Thanks

Yeah, it does have to be four characters long, so "111" or "55555" (for example) shouldn't make it in to the table, as you say smallint would.

I've just changed it to char(4) to remove the chance of "0" through "999", I just can't help but feel there should be something like num(4) to make sure letters can't be entered, although I'm taking care of that server-side, so not vital.

Thanks again

 

[markros]

Look at IsNumeric function in SQL Server. Also you may check

http://www.berezniker.com

and search there for IsNumeric custom aware function.

 

[MarkZK]

ahhh, that'll do it

Thanks

 

[gmmastros]

I would suggest you use the smallint data type and then add a check constraint to it. For example....

Create Table Blorg(MyData SmallInt)

Alter Table Blorg
Add Constraint FourDigitNumber Check(MyData >= 1000 and MyData < 10000)

In this case, you can only have numbers between 1000 and 9999.

If you try to insert a value outside of this range, you will get an error:

Insert Into Blorg(Mydata) Values(1)

[red]Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "FourDigitNumber". The conflict occurred in database "LTD", table "dbo.Blorg", column 'MyData'.[/red]
The statement has been terminated.

Similarly, if you try to update an existing row...

Update Blorg Set Mydata = 1

[red]Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "FourDigitNumber". The conflict occurred in database "LTD", table "dbo.Blorg", column 'MyData'.[/red]
The statement has been terminated.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MarkZK]

Actually, I like the sound of that, I've yet to make a function in SQL, still getting to grips with stored procedures and I'm a complete noob, this looks simpler.

So, I assume to the existing table I just change the DataType of column "LoggedID" smallint (I'm still doing things in the Design window and generally not through statements)

and then execute the statement

Alter Table Logged_UserData
Add Constraint FourDigitNumber Check(LoggedID >= 1000 and LoggedID < 10000)

I think I'll just backup the data first, the "Alter" gets me worried


Thanks a lot George

 

[hmckillop]

George

Your constraint wont work for values of between 0000 and 0999, which are both four digits but not numerically between the range. As Mark says ideally a NUm(4) would be the way to go.

A potential option is to store it as smallint - have the constraint to stop 5 digits e.g. < 10000 and then format it on way out using PATINDEX

e.g.

declare @myField smallint
SELECT @myField =1

Select 
case when len(@myField) = 4 then convert(char(4),@myfield) 
ELSE replicate('0',4 - len(@MyField)) + convert(char,@myField)
END As FieldSize

SELECT @myField =10

Select 
case when len(@myField) = 4 then convert(char(4),@myfield) 
ELSE replicate('0',4 - len(@MyField)) + convert(char,@myField)
END As FieldSize

SELECT @myField =122

Select 
case when len(@myField) = 4 then convert(char(4),@myfield) 
ELSE replicate('0',4 - len(@MyField)) + convert(char,@myField)
END As FieldSize

SELECT @myField =1222

Select 
case when len(@myField) = 4 then convert(char(4),@myfield) 
ELSE replicate('0',4 - len(@MyField)) + convert(char,@myField)
END As FieldSize

Yes I appreciate string functions may not be so speedy, but I think it meets the original requirement

Maybe I am just tired and got it all wrong?


"I'm living so far beyond my income that we may almost be said to be living apart

 

[gmmastros]

so "111" or "55555" (for example) shouldn't make it in to the table

I understood this to mean that the number must be between 1000 and 9999. If the value can be any number less than 10,000, then I would have recommended a different check constraint. If it's important to 'see' a zero padded value, I would add a computed column to the table.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hmckillop]

lol. I agree and probably should have read the other posts a bit better.
However, I suppose mark would need to quantify if he requires "0111" as a valid value as per

I know it'll always be four characters long and range from 0000 to 9999

.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[MarkZK]

Hi Hmckillop,

Yeah, that was originally the case, at first I wrongly thought nchar() stood for numeric character so that nchar(4) would have been any number with the length of four, but I check that with a RegExp in ASP ([0-9]{4}). I would still like that num() datatype though :-D oh well, can't have it all, anywho luckily in this case I'm able to set the range from 1000 - 9999 without any problems, but thanks for the code, I wont pretend I understand it

Thanks again George, and thanks all, it's very good of you all to take the time.

 

[gmmastros]

there are several data types for storing strings.

nchar, char, nvarchar, varchar, ntext, text

the 'n' versions of the data types accommodate unicode characters. Unicode characters require 2 bytes to store a single character. So, a char(4) can store a string up to 4 characters long, and takes 4 bytes to store it in the table. nchar(4) can also store 4 characters, but requires 8 bytes to store in the table.

with char/nchar, the data is stored with spaces added to the end. varchar stands for varying character and is similar to char and nchar. The only 'pracical' difference is the space padding at the end.

text and ntext should be avoided. This is especially true if you are using sql2005 and up. instead of text, use VarChar(max) and instead of ntext, use nvarchar(max).

Hopefully this will clear up any confusion regarding this matter.

There is also another data type you could have used (assuming you also allow values from 0 to 999). The decimal data type allows you to specify precision and scale. precision represents the total number of digits, and scale identifies the number of digits after the decimal point. So a Decimal(4,0) does not allow fractional numbers (they would round to the nearest whole number). Unfortunately (in this case) this also allows for negative numbers. Decimal(4,0) can store numbers in the range -9999 to 9999. Since you probably don't want to allow negative numbers, you would still need to use a check constraint. I stand by my original suggestion of smallint.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MarkZK]

I want to give you another * for all this additional info, George, but it doesn't let me :-( hopefully someone else will

That Decimal() datatype does sound very close, and although I'm happy with how it is right now, out of interest, could I have added .....

Create Table Blorg(MyData Decimal(4,0))
Alter Table Blorg
Add Constraint FourDigitNumber Check(MyData >= 0)

... and still have entered "0000" ?

Thank You !!

 

[gmmastros]

Yes. That would also work (from a functional perspective).

If this were my project, I would keep it as the SmallInt data type. The SmallInt data type requires 2 bytes to store the data. Decimal(4,0) requires 5 bytes to store it.

Please don't think I'm anal about 3 bytes per row. I'm not (Honest). However, all things being equal, why not use the smaller data type, ya know? Actually.... I think it's a little odd that the decimal data type would actually use more storage than a string, varchar(4) or char(4).

I mentioned earlier about the computed column, but realize that I didn't show you how to use it. Apparently, you want to have a zero-padded number. With a number data type, you will lose the zero padding. However, you can create a computed column that can pad the number with zeros. Here's how.

Create Table Blorg(MyData SmallInt)

Alter Table Blorg Add Constraint FourDigitNumber Check(MyData >= 0)

Alter Table Blorg Add MyPrettyData As Right('0000' + Convert(VarChar(4), MyData), 4)

Insert Into Blorg(MyData) Values(1)
Insert Into Blorg(MyData) Values(10)
Insert Into Blorg(MyData) Values(100)
Insert Into Blorg(MyData) Values(1000)

Select * From Blorg

Notice the 4 inserts. We are ONLY adding data to the MyData column. Based on the values were are inserting and the check constraint we have in place, all of the data will get inserted (no errors).

Now, notice the Select *. It shows a 'MyPrettyData' column which is zero padded. This is not a 'real' column. Every time you want to DISPLAY the value to the user, you can use the MyPrettyData column. But... all the other code can use the MyData column and treat it as though it's a number (because it is).

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MarkZK]

Genius, yup, perfect sense, even at 1:15am :-D

great stuff, George, I really appreciate the help, thanks again.