CODEWITHSUNDEEP
stringsubstringprogress-4glopenedge
mac100
mac100

Reputation: 137

How to check if "app" is substring of "gapple" in progress openedge 4gl?

I need to check if "app" is substring of "gapple" or not. I know I can do it using matches but I think its expensive.

Seems lookup and can-do match the whole string?

Upvotes: 1

Views: 5889

Answers (2)

Jensd
Jensd

Reputation: 8011

You can use INDEX, it searches for a string within a string.

DISPLAY INDEX("gapple", "app").

Full usage:

INDEX(source, target [, starting position]).

Returns an INTEGER value that indicates the position of the target string within the source string. If you specify a starting position you start from that position and search right.

If the string isn't present INDEX will return a 0.

If you want to search right-to-left you can use R-INDEX with the same inputs.

About functions and queries

You should if possible avoid to use functions like INDEX in queries. It will make index matching not work and result in table scans (reading of entire tables/indices). Depending of size of table it will potentially take up lots of resources.

Don't do this:

FOR EACH table NO-LOCK WHERE INDEX("searchstring, table.field) > 0:

Instead:

Find a work around. The work around must be adapted for your system and could be one of the following (or something else):

  • Adding a field to your table where you nightly scan the table and update the search result.
  • Transfering a limited number of records to a temp-table and then scanning it (but remember: temp-table index usage counts too).
  • Limiting the query with other clauses and then running INDEX only on those. The example below will scan all todays records but not the rest.
  • Something entirely different that works for you!

Example:

FOR EACH table NO-LOCK WHERE table.date = TODAY: 
    IF INDEX("searchstring, table.field) > 0 THEN DO:
        /* Code goes here */
    END.
END.

Edit: after good comments I added some information about using functions in queries

Upvotes: 4

Tom Bascom
Tom Bascom

Reputation: 14020

As JensD points out "index()" is the usual function for that purpose.

If you are concerned about it being expensive that suggests that you might be thinking about using it in a WHERE clause. Using any function in a WHERE clause is going to be expensive because you are going to force the engine to examine a whole lot of data.

CAN-DO() is especially dangerous in that regards. While it is often subverted as a string function it is actually a client-side security function. One of the side-effects of that is that it cannot be evaluated by the db engine -- it must be evaluated on the client. So all data that can-do() looks at must be passed to the client.

You will find plenty of bad examples to follow but you should not use can-do() as a string function and you should especially not use it in WHERE clauses.

Upvotes: 1

Related Questions