How to replace field values where there is lots of blank spaces between text with only single space?

[jblomqvist]

Hi all,

How to replace field values where there is lots of blank spaces between text with only single space?

I have a Description field that contains values like this, see an example table:

Date	Description
10/12/2019	Bank          America        344556
05/12/2019	Legal&Fin      344556
02/12/2019	Transfer                   Note           Confirm
02/12/2019	634465768 Telephone

As you can see in the example above the Description values have blank spaces between text, how can I replace many of them by just reducing the spaces to 1 space?

[Frank_Hartmann]

maybe like that:

keepchar(Description,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 ')	 as  Description_New

[Vegar]

You can solve this by using a mapping table. Consider this script example:

/*Create a map table consising of multiple blanks as lookup value
single blank as replace value. Important to have the longest blank string first in the map table an the shortest  '  ' last in the map table.*/
map_blank:
mapping LOAD 
  repeat(' ', 100-IterNo()) as A,
  ' '
AutoGenerate 1
while IterNo() < 100;

/*Map the substings of Description using the map table above.*/
LOAD 
*, 
MapSubString('map_blank', Description) as Description_cleaned 
Inline [
Date	Description
10/12/2019	Bank          America        344556
05/12/2019	Legal&Fin      344556
02/12/2019	Transfer                   Note           Confirm
02/12/2019	634465768 Telephone
](delimiter is '	') 
;