On 4/9/07, Kent Belan <[EMAIL PROTECTED]> wrote:
> Hello,
> I need to seek the reverse of a character field.
> I have an account number and the client wants to search by the last X
> characters.
>
> The account number is a character field 20 spaces.
>
> Example: acct #: 12345678
> User enters 678 and finds all accounts that end in 678
>
> Anyone have a good solution ?
Depends on the scale of the thing. Try:
m.lcSearchString = "678"
LOCATE FOR RIGHT(ALLTRIM(Table.cAccountNo), len(lcSearchString)) =
lcSearchString
Net cost: nothing. The power of Visual FoxPro might amaze you.
On the other hand, if this is on a busy lan with 300 workstations and
a million rows in the account table, you'll probably see performance
issues. Optimize by creating indexes for the length they'll need with
expressions like:
RIGHT(PADL(cAccountNo,20),3)
RIGHT(PADL(cAccountNo,20),4)
RIGHT(PADL(cAccountNo,20),5)
(I avoid ALLTRIM() in index expressions out of superstition, even
though these should all be fixed length index expressions. I had a bad
experience.)
Then, write your code so *THE EXPRESSION ON THE LEFT EXACTLY MATCHES
THE INDEX EXPRESSION* (hint: this is important; Rushmore doesn't work
without it.
DO CASE
CASE len(lcSearchString) = 3
LOCATE FOR RIGHT(PADL(cAccountNo,20),3) = m.lcSearchString
CASE len(lcSearchString) = 4
LOCATE FOR RIGHT(PADL(cAccountNo,20),4) = m.lcSearchString
etc.
--
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
