Extract words from a free text field containing a description and adding those words one by one to another table

[DoctorPolidori]

I need some ideas to build a LOAD script to extract words from a free text field containing a description and adding those words one by one to another table

A pseudo code could look like this:

for each description_field in table_A
    for each description_word in description_field 
        insert word in table_B
    next description_word
next description_field 

[Oleg_Troyansky]

Hi,

You can use the function SubField, with the space as a delimiter - that will break the text into individual words. Then, you should probably worry about punctuation characters - commas, periods, etc, and remove them using the function PurgeChar().

Cheers,

[marksouzacosta · Accepted solution]

Hi @DoctorPolidori ,

SubField is the function you are looking for.

Here is an example:

// Source Sample data
Source:
LOAD * INLINE [
RecordNo|Description
1|Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
](delimiter is '|');

// Description will be splited by space
WordsWithCommmaTable:
LOAD
  RecordNo,
  Subfield(Description,' ') AS WordWithComma
RESIDENT
 Source
;

As @Oleg_Troyansky suggested you can do further clean-up using PurgeChar or other funcionts to remove unwanted characteres.

This is how the table WordsWithCommaTable looks like: