extracting part of a string 2

[mwmark]

I am trying to split a string and extract the numbers left of the first 3 consecutive letters.

data:
1234567abc4k4k4w
1234abc656565sdjfs
123456789xyza56566

desired output
123456
1234
123456789

fyi:crystal 10, sql db,

Any help would be appreciated

 

[bdreed35]

There may be a more elegant way to do this, but here is my first solution:

local stringvar input := {table.field};
local stringvar output := input;
local numbervar strlen := length(input);
local numbervar x;

for x := 1 to strlen
do
(
if strlen > 2 and
x <= strlen - 2 and
input[x] in ["a" to "z"] and
input[x+1] in ["a" to "z"] and
input[x+2] in ["a" to "z"]
then (output := left(input,x-1);
x := strlen + 1
);

output

~Brian

 

[lbass]

Or you could use:

stringvar x := {table.field};
numbervar i;
numbervar j := len(x);
stringvar y;

for i := 1 to j do(
if isnumeric(x) then
y := y + x;
if not isnumeric(x) then
exit for
);
y

-LB

 

[mwmark]

lbass,
this works but does not reset after each record
how do i reset it?
the output adds each routine output together

 

[bdreed35]

The variables are defaulting to a scope of global.
You can change them to local like this:

local stringvar x := {table.field};
local numbervar i;
local numbervar j := len(x);
local stringvar y;

for i := 1 to j do(
if isnumeric(x) then
y := y + x;
if not isnumeric(x) then
exit for
);
y

~Brian

 

[lbass]

Oops. y should have been set to "":

stringvar x := {table.field};
numbervar i;
numbervar j := len(x);
stringvar y := [red]""[/red];

for i := 1 to j do(
if isnumeric(x) then
y := y + x;
if not isnumeric(x) then
exit for
);
y

Or you could make them local, as Brian suggested.

-LB