Mapsubstring only whole words?

[therealdees]

Hi!

I'm having issues using MapSubstring() to remove prepositions substrings from a string.

ReplacePreposition:
Mapping LOAD * Inline [
A,B
" da "," "
" de "," "
" do "," "
" das "," "
" dos "," "
"da "," "
"de "," "
"do "," "
"das "," "
"dos "," "
" em "," "
" por "," "
" até "," "
" ate "," "
" e "," "
" km. "," "
" km "," "
];

SET vReplacePreposition = MapSubString('ReplacePreposition', cliente_rua_temp);

TempTab:

If(Not Match(Capitalize($(vReplacePreposicao)), $(vReplacePreposicao)), 'ENTRADA MANUAL') as x

From y

Where z;

After setting the table and a variable with the function, I call it during the table load to replace these 'special' substrings to compare the result. In this example, I'm checking if the word capitalization match.  'Prepositions' are not capitalized normally, so I exclude them for a fair comparison.

Note that in the map table, the strings are written considering leading and trailing spaces so I set the Verbatim = 1 to do so. Obviously the strings with both leading and trailing spaces will be perfectly replaced, the problem comes to the strings with only trailing spaces that will replace whatever string  with that ending, for instance:

Full string: Aparecida Moreira Basso

After mapping: Rua Apareci Moreira Basso

Is there a way to replace only whole words, preferably not changing much of this logic?

[therealdees · Accepted solution]

It's kinda stupid actually, and quite obvious. I just had to add a trailing and leading space to the string so every substring will be " x " and therefore I won't need to specify 3x each condition:

String = "Aparecida do Norte"

Fixed = " Aparecida do Norte "

Now the replace table will only have values with spaces as well:

" da " instead of "da " and " da" and " da "