Mark, Perfect! Thanks!!
I altered my table to specify ZEROFILL and that did the the trick. -- George >>>-----Original Message----- >>>From: Mark Leith [mailto:[EMAIL PROTECTED] >>>Sent: Wednesday, August 09, 2006 3:56 PM >>>To: Jonathan Mangin >>>Cc: George Law; MYSQL General List >>>Subject: Re: forcing leading 0 for numeric fields >>> >>>Jonathan Mangin wrote: >>>> ----- Original Message ----- >>>> From: "George Law" <[EMAIL PROTECTED]> >>>> To: "MYSQL General List" <mysql@lists.mysql.com> >>>> Sent: Wednesday, August 09, 2006 3:40 PM >>>> Subject: forcing leading 0 for numeric fields >>>> >>>> >>>> Hello All, >>>> >>>> I have what is probably a simple question. >>>> >>>> I have a table of phone numbers, broken into npa,nxx,station >>>> >>>> So, 8001231234 npa =800 nxx=123 station=1234 >>>> >>>> Some queries pull data from this table in the format: >>>> >>>> select * from table where concat(npa,nxx,station)=8001231234 >>>> >>>> >>>> That is all good. >>>> >>>> The problem I ran into today is where the station column >>>is < 1000, >>>> ie 8001230123 >>>> >>>> station =0123 which gets stored as 123 by mysql >>>> >>>> >>>> >>>> Is there a quick and easy way to force station to 4 digits >>>when I do the >>>> query >>>> select * from table where concat(npa,nxx,station)=8001230123 >>>> >>>> This query does not work, but select * from table where >>>> concat(npa,nxx,station)=800123123 >>>> >>> >>>Store them as INT with ZEROFILL: >>> >>>mysql> CREATE TABLE zeros (i INT(4) ZEROFILL, j INT(4) ZEROFILL, k >>>INT(4) ZEROFILL); >>>Query OK, 0 rows affected (0.09 sec) >>> >>>mysql> INSERT INTO zeros VALUES (23,3244,0123); >>>Query OK, 1 row affected (0.01 sec) >>> >>>mysql> select * from zeros; >>>+------+------+------+ >>>| i | j | k | >>>+------+------+------+ >>>| 0023 | 3244 | 0123 | >>>+------+------+------+ >>>1 row in set (0.08 sec) >>> >>>Make sure you specify the length of the digits that you would like >>>padded to within the INT specification. >>> >>>Best regards >>> >>>Mark >>> >>>-- >>>Mark Leith, Support Engineer >>>MySQL AB, Worcester, England, www.mysql.com >>>Are you MySQL certified? www.mysql.com/certification >>> >>> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
