Hello Foc Wizards - I have an easy one for you here.

What is the best/most efficient way to exclude records in selection criteria that end with a certain alpha letter.

For example I have an A22 field that can contain a mixture of numbers/letters but I want to exclude records ending in 'P' or 'V'.

From the list below, the field length may vary (max 22) but I would want to exclude the 1st two records, but keep the third:

ABC12345P
PVC123V
123PV123456A

Thanks!

This message has been edited. Last edited by: kadkins,

Hi,


This should translate well into any SQL and should be ok with performance..

  
TABLE FILE CAR
PRINT
CAR
WHERE NOT (CAR LIKE '%A' OR CAR LIKE '%N')

END
-EXIT

Thanks. Works like a charm within Oracle.

Aaaah ... One of my old favourites!

Use REVERSE to reverse the order of the string and then EDIT to find that pesky ending character, which will now be the first character. It doesn't matter how long the string is.

Hi George,
I was remembering a method I used in the past when I saw you mention REVERSE which, I haven't used before. I tested it and found it works but, you must perform an extra step. After the REVERSE, any spaces that were padding the original string to the right are now moved to the left. An LJUST or TRIM will fix that.

The good news is both methods work. Actually, the method Severus.snape offered also works so, KADKINS has 3 choices to play with to determine which is the most efficient in their environment.

Here's a CAR table example showing my old method using ARGLEN and PARAG and your method using REVERSE, LJUST, and EDIT:

TABLE FILE CAR
PRINT CAR
COMPUTE CARA1/I3=ARGLEN(16,CAR,CARA1);AS ARGLEN
COMPUTE CARA2/A1=SUBSTR(16,CAR,CARA1,CARA1,1,CARA2);AS SUBSTR
COMPUTE CARA3/A16=REVERSE(16,CAR,CARA3);AS REVERSE
COMPUTE CARA4/A16=LJUST(16,CARA3,CARA4);AS LJUST
COMPUTE CARA5/A1=EDIT(CARA4,'9');AS EDIT
ON TABLE PCHOLD FORMAT DHTML
END

I tried each solution offered with success using each technique. Once again, Focal Point proves to be a treasure trove of info and... there's more than 3 ways to skin a cat.

Thanks again!