Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Replacing consecutive zeros in a string with a period 2

Status
Not open for further replies.

klgoist

IS-IT--Management
Dec 3, 2012
12
US
I have a string field that is 17 characters long. (example: 04000000000000039 or 04000000000010439).
I would like to replace the zeros between the 4 and the next non zero character with a period. (Example: 04.39 or 04.10439)

Is there a simple formula for doing this?
 

We could probably tighten this up a bit, but it works for your sample data:

Code:
stringvar v_source := {YourDatabaseField};
stringvar v_result;
numbervar x := 1;

while x <= len(v_source)
do
(
if x = 1 and v_source[x] = "0"
then
v_result := v_result + v_source[x]

else

if v_source[x] <> "0"
then
v_result := v_result + v_source[x]

else

if  v_source[x] = "0"  and v_source[x+ 1] <> "0" and  (v_result like "*.*") then
v_result := v_result + v_source[x]

else

if  v_source[x] = "0"  and v_source[x+ 1] <> "0" and not (v_result like "*.*") then
v_result := v_result + "."

else

if  v_source[x] = "0"  and v_source[x+ 1] = "0" then
v_result;
x := x + 1
);

v_result

Please test thoroughly.

 
Brian, you are on the right track, but when I ran this against my database, the 1st result was spot on, but the second result piggy backed on the first result and so on.

1st result = 04.10439
2nd result = 04.1043904010439
3rd result = 04.104390401043904010439
and so on until it reached a maximum string length of 65,000+

any thoughts?
 
This suggestion is a little odd but give it a shot. I sometimes tend to overuse split so this may be the case here lol..

I used six "0"'s in the split functions.... That may need to be adjusted based on your fields

Code:
split({@YourDBField},"00000")[1]
& "." &
totext(tonumber(split({@YoourDBField},split({@YourDBField},"00000")[1]&"0")[2]),"#")

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 
Just modify second line of Brian's formula

stringvar v_source := {YourDatabaseField};
stringvar v_result:= '';
numbervar x := 1;

Ian
 
Ian / Brian,
Thank you for the help. Ian's modification to Brian's code gave me exactly what I needed.

Ken
 
ok, so I started digging deeper into my results from the above code and here where I'm at.

04000000000000039 returns 04.39
04000000000010439 returns 04.10439
but when I hit on numbers like
04000000000300600 the result I get back is 04.3060, but of course this need to be 04.300600

Any suggestion how I might keep the repeating zero's that occur after the 2nd non-zero number?
 
Again, please keep testing but this works for the three sample values - I just commented out one line that addressed the repeating zeroes after the decimal had been inserted:


stringvar v_source := {YourDBField}
stringvar v_result := "";
numbervar x := 1;

while x <= len(v_source)
do
(
if x = 1 and v_source[x] = "0"
then
v_result := v_result + v_source[x]

else

if v_source[x] <> "0"
then
v_result := v_result + v_source[x]

else

if v_source[x] = "0"
//and v_source[x+ 1] <> "0"
and (v_result like "*.*") then
v_result := v_result + v_source[x]

else

if v_source[x] = "0" and v_source[x+ 1] <> "0" and not (v_result like "*.*") then
v_result := v_result + "."

else

if v_source[x] = "0" and v_source[x+ 1] = "0" then
v_result;
x := x + 1
);

v_result
 
Thank you Brian, I was so close to getting this. I was actually playing with that part of the code, but I hadn't thought to comment it out.
 
the code I gave you returns 04.300600 for 04000000000300600 ...

Im just sayin

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top