Splitting the words in a field

[#1]

I am having a qvd file having field name colors. This field is consist of accumulated records of color names

example RE, BLRE.. where BL and  RE is seperate color names. I want to split these names into another record and store in a temp .

Also i want to generate unique color ID for the new born records.

[rwunderlich]

1. Are all the color names two characters?

2. Are all the possible color names known in advance?

-Rob

[tresesco]

Probably you need Subfield() function in combination with another string fucntion Left(). Try something like:

Load

          Subfield( Left(COLORS,2)& ';' &Mid(COLORS,2) , ';' ) as NewCOLORS

[#4]

Yes. Color Names are tow characters concatenated within one field (column)

And all the color names are known to me, I just want to split the word in two characters.

Thank you in advance

[#5]

Expected Result was

BUDPLPORVI  into   BU

                               DP

                               LP

                               OR

Source Code:

TEMP_COLOR:

LOAD CODE,

COLORS

Resident INVENTOR_QVD

Where COLORS <> '';

LOAD

Subfield( Left(COLORS,2)& ';' &Mid(COLORS,2) , ';' ) as NewCOLORS

Resident TEMP_COLOR;

Drop Table TEMP_COLOR;

[tresesco · Accepted solution]

Well now the requirement is clearer. Try like:

Load

       Mid(COLORS, (Iterno()-1)*2+1, 2) as NewCOLORS

From <>

While Len(COLORS)>=IterNo()*2;

[#7]

Thank You Very Much. Its really working