Using MapSubstring() to edit strings

The MapSubstring() function is a powerful alternative to using nested Replace() or PurgeChar() functions.

MapSubstring(), unlike it’s siblings ApplyMap() and Map, will apply multiple mappings from the mapping table. Here’s an example.

ReplaceMap:
MAPPING LOAD * INLINE [

char replace
)
)

,
/

] (delimiter is ‘ ‘)
;


TestData:
LOAD
*,
MapSubString(‘ReplaceMap‘, data) as ReplacedString

;
LOAD * INLINE [

(415)555-1234
(415)543,4321
“510”123-4567
/925/999/4567
] (delimiter is ‘ ‘)
;


In field “ReplacedString“, all the characters matching the first field of the map (“char”) are replaced with a backslash as shown in this table. This makes it ready for parsing with a function like SubField().

Another usage is an alternative to nested PurgeChar() to remove multiple characters. A blank is used as the mapping character. For example:

PurgeMap:
MAPPING LOAD * INLINE [

char replace
)
)

,
/

] (delimiter is ‘ ‘)
;



MapSubString(‘PurgeMap’, Data)
will produce results like this:

-Rob

6 thoughts on “Using MapSubstring() to edit strings”

  1. Hola
    Tengo dos tablas, y ambos tienen fechas.

    Tabla1
    mes1
    Ventas1

    Tabla2
    mes2
    Ventas2

    y necesito que al apretar mes1 me traiga las Ventas1 y las Ventas2 para poder restarlas.

    De antemano gracias.

  2. nested PurgeChar? not required, since the syntax is PurgeChar({item},'{string made up of characters to be purged}’), i.e. PurgeChar(‘Hello There’,’elo’) will return ‘H Thr’ — you don’t have to do multiple single-character purges.

  3. Any thoughts on how to remove tabs from data (e.g. Chr(9) or t)??

    Thanks for the post – always helpful!

  4. The easiest way to remove tabs is with
    PurgeChar(string, chr(9))

    If you want to map them instead, you can include them in your mapping table like this:

    MAPPING LOAD
    chr(9) as char, ‘x’ as replace
    AUTOGENERATE 1
    ;

    You can still use the INLINE table and the addition will be concatenated.

Comments are closed.