Replacing consecutive zeros in a string with a period 2

[klgoist]

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?

 

[briangriffin]

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

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.

 

[klgoist]

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?

 

[CoSpringsGuy]

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

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

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[IanWaterman]

Just modify second line of Brian's formula

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

Ian

 

[klgoist]

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

Ken

 

[klgoist]

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?

 

[briangriffin]

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

 

[klgoist]

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.

 

[CoSpringsGuy]

the code I gave you returns 04.300600 for 04000000000300600 ...

Im just sayin

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection