1. 

   elb98rm, Member

   Hi,

   I've been attempting to add leading zeros to a field. I'm not sure, but I think mydbr is removing them.

   I'm doing the following:

   ...
   Select LPAD(v.id, 6, '0') as 'Id'
   ...

   LPAD seems to be working, as if you run:

   * LPAD(v.id, 2, '0') as 'Id'

   Then the item is truncated as you'd expect.

   Do you have any suggestions?

   Cheers
   Rick
2. 

   myDBR Team, Key Master

   Using LPAD should work just fine. The column is converted to a string which myDBR will display as is.

   --
   myDBR Team
3. 

   elb98rm, Member

   I have discovered the problem.

   In order to remove commas, I was using:

   select 'dbr.colstyle', 'Id','%d';

   This was truncating the leading zeros.

   Cheers
   Rick
4. 

   myDBR Team, Key Master

   Rick,
   if you run the updater and get the latest build, you will be able to use column style (printf) to format the leading zeros:

   select 'dbr.colstyle', 'Id','%06d';

   By using column style you will be able to separate the formatting from the actual query and keep your SQL clean (select id)

   --
   myDBR Team
5. 

   elb98rm, Member

   Top tip!

   Thanks.

   EDIT:

   D'oh! That adds back in the commas. How do I remove them in this case?
6. 

   myDBR Team, Key Master

   Run the updater to get the lastest build.

   We removed the thousand separators in case where leading zeros were applied using column formatting.

   --
   myDBR Team
7. 

   elb98rm, Member

   Nice. I thought I was on the latest build.
   Good job!

Reply

You must log in to post.