logo
Powered by QM on a Rpi server
Home
About OpenQM
Sales and Downloads
Help and Support
About
Login

KnowledgeBase 00038: The SUBSTITUTE() Function

Last updated: 17 Feb 2017
Applies to: All versions
Search  
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

None.



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