3

I have a table with auto increment zerofill ID numbers. When I query the data the IDs lose their leading zeros (i.e. "000529" returns as "529"). Is there a way to preserve the leading zeros, or even generate them back in the query statement? I know I can generate them back in PHP using STRPAD, but for the specific project I am on I would like to retrieve the data as it is in the DB.

Evan4623
  • 259
  • 1
  • 4
  • 14
  • It's probably PHP that casts these ID's to integers. – Mchl Sep 14 '10 at 18:19
  • They are preserved here, so you'll need to give more information about how you connect, query, and possibly cast (make sure you get back strings, not integers). `mysql` & `PDO` will preserve them in a quick test here 'out of the box'. – Wrikken Sep 14 '10 at 18:24
  • SELECT id, name title, 'profile' type, date FROM intake WHERE name LIKE '%mi%' UNION ALL SELECT id, casename, 'matter' type, date_opened date FROM s_matters WHERE casename LIKE '%mi%' UNION ALL SELECT cid AS id, name title, 'call' type, date FROM s_calls WHERE name LIKE '%mi%' UNION ALL SELECT id, subject title, 'event' type, start_date date FROM s_events WHERE subject LIKE '%mi%' ORDER BY date DESC LIMIT 20 – Evan4623 Sep 14 '10 at 19:44
  • the id fields and cid fields are 6-8 digits with leading zeros, but when the result is returned from that query, there are no leading zeros in any of the ids – Evan4623 Sep 14 '10 at 19:45
  • Which MySQL library are you using? mysql_*(), mysqli_*() or the PDO class? – Alex Jul 28 '14 at 15:40
  • In mysql, do you use the `Int` type, `char` type or `Varchar` type as data type in the database, have you also confirmed that the leading zeros persist in the database – Adibas03 Feb 28 '17 at 10:50

3 Answers3

8

Use function LPAD() to show the numbers (left) padded with zeros:

SELECT LPAD( 529, 6, '0') AS padded;
Rajib Sarker
  • 91
  • 3
  • 6
0

It's the UNION ALL part that is converting them to INT - I've yet to find a simple solution. To my mind it's a mySql bug.

anoldermark
  • 366
  • 3
  • 8
0

When you select the field with leading zeros and use a UNION it will remove the leading zeros.
I found a workaround by adding: 

CONVERT(id_number,char) as id_number

Obviously, id_number represents whatever zero filled field you are trying to SELECT.

סטנלי גרונן
  • 2,917
  • 23
  • 46
  • 68
Douglas Mott
  • 1