String Extract 1

[maccrystal]

Crystal XI, SQL2000
30 Char String field (CODELIST), they contains several 2 char codes, with no seperator values. Need to get the 2 char code and lookup the Text value.

Rec 1: AABBCCDDEE
Rec 2: AACCDD
Rec 3: Blank or null

"AA" = Part 01
"BB" = Part 02
"CC" = Part 03
etc...

Result should look like
Rec 1art 01, Part 02, Part 03
Rec 2art 01, Part 03, Part 04
Rec 3:" *no Code *"

http://www.appstratinc.com

 

[beltmanjr]

try the instr function. but you'll need to specify the strings manually...

 

[lbass]

stringvar x := {table.string};
numbervar i;
numbervar j := len(x);
stringvar y := "";
for i := 1 to j step 2 do(
if x = "A" then
y := y + " Part 01," else
if x = "B" then
y := y + " Part 02," else
if x = "C" then
y := y + " Part 03," else
y := y + " Other,"
);
if isnull({table.string}) or
trim(x) = "" then
"*no code*" else
trim(left(y, len-1))

-LB

 

[maccrystal]

The x only returns the first Character of the two char code instead of "AA". So the formula will only consider the first character of the code structure. How do you get the X index to return "AA" or "BB" instead of just "A" or "B"?

http://www.appstratinc.com

 

[maccrystal]

Here is my Hack for this:
if x+x[i+1]

Returns "AA"

http://www.appstratinc.com

 

[beltmanjr]

Why not use a function that is build to detect strings in strings?

stringvar x := {table.string};
stringvar y;

if instr(x, 'AA') > 0 then
    y := y + ' Part1,';
if instr(x, 'BB') > 0 then
    y := y + ' Part2,';
if instr(x, 'CC') > 0 then
    y := y + ' Part3,';
if instr(x, 'DD') > 0 then
    y := y + ' Part4,';
if instr(x, 'EE') > 0 then
    y := y + ' Part5,';

if IsNull({table.string}) or trim(x) = "" then
    "*no code*" 
else
    trim(left(y, len(y)-1))

 

[lbass]

The example you showed always had pairs of letters like AA, BB, CC, etc., and my formula would work if that were the case--so how is your actual data different than you originally presented it?

-LB

 

[maccrystal]

AAALAHBACBC1

http://www.appstratinc.com

 

[beltmanjr]

Lol, this is just like the other thread.. Please make certain to explain your issue well and provide examples of data that are accurate.

 

[lbass]

In the future please show sample data that actually reflects your real data. My time was wasted coming up with the original solution.

Do you have a working formula now?

-LB

 

[maccrystal]

The formula is working based on your solution. Please except my apologies if you think it was a waste of your time, your direction was a great help.. Thanks

http://www.appstratinc.com