Powered by QM on a Linux server
Help and Support

KnowledgeBase 00038: The SUBSTITUTE() Function

Last updated: 27 Dec 2018
Applies to: All versions
Top level index       Full Index Search Tips
Previous article     Next article

Most implementations of the multivalue Basic programming language do not provide a function to substitute items from one multivalued list with corresponding items from a second list. The SUBSTITUTE() function in QMBasic can do this, even where the original data is itself multivalued.

An Example

An order processing system has a sales record in which there is a set of three associated multivalued fields storing the item part number, the quantity ordered and the status of the order for each part. For example:

  Part   Qty   Status 
  101      2     D 
  106      7     P 
  317     12     D 
In this example, the status column has values D for delivered and P for pending. A real usage may have many other codes.

What is needed is a function that will allow this record to be displayed as

  Part   Qty   Status 
  101      2   Delivered 
  106      7   Pending 
  317     12   Delivered 
The SUBSTITUTE() function can do this. If the status column is a field named STATUS, the required expression is
   SUBSTITUTE(STATUS, 'D,P', 'Delivered,Pending', ',') 
The function takes four arguments:
  • The input data, possibly multivalued.
  • A list of the possible input data values.
  • A list of the corresponding output data values.
  • The delimiter used to separate the values. If this is omitted, it defaults to a value mark.

Related Articles


Please tell us if this article was helpful
Very     Slightly     Not at all
Email (optional)