Delete Space in Data

[Esneider]

Hi,

In one of my fields, I have data like '    XXXX   YYY   ZZ' and I want them as 'XXXX YYY ZZ'

How can I do that?

[#2]

Hi,

Use functions to remove spaces: Replace () and LTrim ().

LTrim() : Returns the string trimmed of any leading spaces.

Use :

Replace(LTrim('   XXXX   YYY   ZZ'),'  ',' ')

Regards,

Snehal Nabar

[Anonymous · Accepted solution]

This is an example of Rob Wunderlich's technique, check CLEANED_TEXT after cleaning from spaces.

SpaceMap:

MAPPING LOAD repeat(' ', RecNo()), ' '
AutoGenerate 10  // up to 10 spaces
;

SAMPLE:
LOAD * Inline
[TEXT
HELLO AND            WELCOME
THIS IS A SAMPLE TEST
I       AM NOT SURE
];

DATA2:
LOAD MapSubString('SpaceMap', trim(TEXT)) as CLEANED_TEXT
Resident SAMPLE;

I hope this helps!

MultiView
 

[rwunderlich]

@MultiView, thanks for posting the excellent example!

@Son of Sardar, The MAPPING LOAD generates a standard two column mapping table that maps n blanks to one blank. It uses the repeat() function to generate 1 blank on the first row, 2 blanks on the second row and so on -- up to 10 blanks (AutoGenerate 10). The MapSubstring() function uses that mapping table to translate groups of blanks in the text to a single blank.

-Rob

[Esneider]

Excellent.

Thanks Rob Wunderlich.

[rwunderlich]

Please mark an answer as correct to close the thread.

-Rob