Powered by QM on a Rpi server
About OpenQM
Sales and Downloads
  Get QM Today
  Current Downloads
  Evaluation Licence
  What's New
  Resources and Solutions
  Pay Invoice Online
  Archived Releases
  Activate Package Trial
Help and Support

KnowledgeBase 00038: The SUBSTITUTE() Function

Last updated: 22 Jul 2016
Applies to: All versions
Top level index       Full Index Search Tips
Previous article     Next article

This article was originally published as a Tip of the Week.

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)