Print Page | Close Window

Need to drop leading zeros

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6822
Printed Date: 04 May 2024 at 6:30am


Topic: Need to drop leading zeros
Posted By: GulfSniper91
Subject: Need to drop leading zeros
Date Posted: 22 Jun 2009 at 1:59pm
I have a label that pulls a fixed 5 digit store number from my SQL database that uses leading zeros (i.e store #4 = 00004, store # 222 = 00222).   With a different customer, the label had been setup to add preceding zeros, now I want to remove all zeros so that I only get 3 digits.  Here's my thinking, but something isn't working.

local numberVar v_length := 0;
local stringVar v_store := "";

// get the length of the store number field
v_length := Length({customer.strnum});

// evaluate the store number length, if less than three, append zeroes at the beginning
if (v_length = 3) then
    v_store :={customer.strnum}
else if (v_length = 2) then
    v_store :="0" + {customer.strnum}
else if (v_length = 1) then
    v_store :="00" + {customer.strnum}
else
    v_store := "000";

v_store;


Replies:
Posted By: JohnT
Date Posted: 22 Jun 2009 at 2:07pm
What is it that isn't working ?   Your description of the problems says you want to remove zeros to only get 3 characters but your code looks at the length of strnum and appends zeros to make 3 characters. 
 
Would you give us some more information ?

Posted By: DBlank
Date Posted: 22 Jun 2009 at 2:09pm
Why not use
tonumber({customer.strnum}) to remove all zeros
 
or to limit it to 3 right characters
right({customer.strnum},3)

Posted By: GulfSniper91
Date Posted: 22 Jun 2009 at 2:17pm
Man you guys are fast with the replies. 

I have to append in some cases like store number 4 which would be 00004 in my database.  I'm thinking that any function I use that comes before the appending of zeros to make exactly 3 digits would need to strip all zeros (like when you make a number an int instead of varchar).

Posted By: DBlank
Date Posted: 22 Jun 2009 at 2:27pm
Either approach below assumes you have no records that numerically would be >999...
You could do: Right("00" + {customer.strnum},3)
 
Or I think your last statement is what  is messing it up..
local numberVar v_length := 0;
local stringVar v_store := "";

// get the length of the store number field
v_length := Length({customer.strnum});

// evaluate the store number length, if less than three, append zeroes at the beginning
if (v_length = 3) then
    v_store :={customer.strnum}
else if (v_length = 2) then
    v_store :="0" + {customer.strnum}
else if (v_length = 1) then
    v_store :="00" + {customer.strnum}
else
    v_store := right({customer.strnum},3);

v_store;

Posted By: GulfSniper91
Date Posted: 22 Jun 2009 at 2:53pm
Your recommendation of

right({customer.strnum},3)

is what I used to make this work.  Thanks for such a quick answer.


Print Page | Close Window