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)