|Powered by QM on a Rpi server|
KnowledgeBase 00038: The SUBSTITUTE() Function
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 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 DIn 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 DeliveredThe 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: