search with like -exact number of numeric characters in a memo field

[pam1980]

Hello,

I am looking to get any string that starts with a fixed alphabet followed by a hyphen and any 5 numeric characters like this
"A-#####". This is a memo field.

How would that work. The usual '?' or '*' wildcards won't work in this case - I searched for this but could not find a solution.

Thank you

 

[lbass]

Try one of these formulas:

mid({table.memo},3,5)

If there can be more than 1 alpha character at the beginning, try:

stringvar x := {table.memo};
if instr(x,"-") <> 0 then
left(split(x,"-")[2],5)

-LB

 

[pam1980]

Thanks lbass - you are always very helpful.

I'd like to rephrase: I need to pull all relevant fields from a table where the memo field contains this string and only these many characters: "A-#####". The hashes will always be numeric characters and there will always be exactly 5 numeric characters after the hyphen.

It could appear anywhere in the memo field. It will always be in the above format. I am not sure how I could instring in such a scenario.

My quandary is that when I do like wildcard characters, I get memo fields where there is 'A' hyphen something else. I only want those record where it is exactly "A-#####".

I hope that is clearer - I have searched but can't find anything appropriate to use.

Thanks again

 

[lbass]

Try this as your selection formula. It will be slow, but should work:

stringvar x := {table.memo};
stringvar array y := split(x," ");
numbervar i;
numbervar j := ubound;
numbervar k;
stringvar array z;
for i := 1 to j do(
if y like "A-*" and
len(y) = 7 and
isnumeric(right(y,5)) then (
k := k + 1;
redim preserve z[k];
z[k] := totext({table.uniqueID},"0000")
));
totext({table.uniqueID},"0000") in z

You should replace {table.uniqueID} with an ID field that is unique to the row containing each memo field. If it is a string, remove the totext(,"0000"). The zeros should match the maximum length of the ID field.

-LB

 

[pam1980]

Thanks lbass - I wrote a customized sql command after looking at a book to speed it up. But thank you so much since your code helped give me an idea of how to proceed.

 

[lbass]

Please post what you ended up using so that I and others can learn from it.

-LB

 

[pam1980]

SUBSTR(replace(TABLE_.FIELD,' ','')), INSTR(replace(TABLE.FIELD,' ',''),'A-')+2,5) = to_char(SUBSTR(replace(TABLE.FIELD,' ',''), INSTR(replace(TABLE.FIELD,' ',''),'A-')+2,5)