leading zeros in a char column 1

[Itshim]

I have a char() column in a table to store zip codes. I noticed that if the zip code is '08062', MySQL will strip off the leading zero. Now if I add anything to the zip code that is not an integer, say a hyphen, or space MySQL will keep the leading zero.

I have thought of a work around, in that I can add a space to the end of any incoming value, then when I select the data out of the table MySQL will strip off any white space at the end because the column type is char(). I was just hoping that there was some way to really achieve what I want, and not resort to work arounds.

TIA,
Itshim

 

[r937]

strip off the leading zero? no way

create table test_char
( id tinyint not null primary key auto_increment
, foo char(37)
);
insert into test_char (foo) values
 ('08062')
,('08062  ')
,('08062-1234')
;
select id,foo,length(foo) from test_char

id   foo      length(foo)
 1  08062        5
 2  08062        5
 3  08062-1234  10

as you can see, mysql strips trailing blanks even for CHAR fields, not just VARCHAR

but leading zeroes in a string? no

not unless you inserted an integer!!

insert into test_char (foo) values
 ( 08062 )
;
select id,foo,length(foo) from test_char

id   foo      length(foo)
 1  08062        5
 2  08062        5
 3  08062-1234  10
 4  8062         4

see the difference? the leading 0 on an integer isn't really there, and the integer-to-char conversion that must take place won't put it back

r937.com | rudy.ca

 

[Itshim]

You are absolutely correct, my apologies...

I was inserting the data through PHP and there was code which I had determining whether quotes needed to be placed around a value, by testing if the value was numeric. I had not considered that the value was being placed in a char() column.

Thank you,
Itshim