Data type for number beginning with 0?

[davidhardie]

Hi, I need to know if there is a datatype in SQL that will allow me to store a number like 01234 without removing the 0 from the start.
I have tried INTEGER AND VARCHAR but they both take the 0 away.
Thanks.

[DMacedo]

Integer is definetly not what you want, that will store the number in int value and remove any leading zeros.
Varchar is a variable character map and will use only what is necessary, so 01234 will only store 1234 in the same way will 0000001234; this is to save your database size.

Use CHAR() field which is fixed and will store your 01234 exactly

Cheers

[davidhardie]

Thanks but that didnt work unfortunately, did the same as before. Could it be something to do with the fact that i am using a MySQL server and its syntax may be different?

[DMacedo]

I am refering to MySQL.

Check your script, if your are storing the number as int it will not work, use a string:

<?
$number = 01234; // is wrong
$number = '01234'; // will work
?>

[r937]

VARCHAR will store exactly what you give it, it will not strip the zeroes

you could use INTEGER(5) UNSIGNED ZEROFILL

but if you're not going to add these numbers up, or find their averages, which is unlikely if you want these numbers used like a product code or something, then i would use VARCHAR

[DMacedo]

I had the idea VARCHAR would strip the leading zeroes, which he confirmed, so maybe it's his script that sends SQL an INT type.

I did think of setting UNSIGNED ZEROFILL to mysql INT type, but filling with leading zeroes if the number is smaller seems relevant in his case.
Either varchar or char would work.

[swampBoogie]

VARCHAR will store exactly what you give it.

No, Mysql removes trailing spaces when insert into varchar columns.

[r937]

the rest of the quote was "it will not strip the zeroes"

i was referring to the stripping of the zeroes, VARCHAR will store exactly all zeroes you give it

but of course you are right, blanks do get stripped

[davidhardie]

Sorry, but i got it now. It was my script. Never changed the string to VARCHAR when i changed the type. Thanks all. I am enlightened! dh.