Subfield with space as delimeter

[Black_Hole]

Hello all,

In my table, I have a field having values with this kind of syntax:

Var0= T025798    2019 194918225     MOTO AME

I would like to recuperate in a variable each value.

For example: Var1=T025798 & Var2=2019 & Var3=194918225 etc.

So I tried the function subfield in my script:

SubField(Var0,' ',1) AS Var1,

SubField(Var0,' ',2) AS Var2,

SubField(Var0,' ',3) AS Var3,

etc.

But I obtain a result only for the first, Var1.  I think that this is due because the length between the values is not equal.

Please could you help me to resolve my problem.

Thank you in advance for your help.

[awaisqureshi]

Seems like either subfield or left/right should get you the result you're looking for.

Can you share sample data?

[kuba_michalik · Accepted solution]

It's not about the unequal length, it's because there is more than one space between the values. Basically Subfield(Var0,' ',2) will return what's between the first and second space (the character). And obviously there is nothing there because one is right after the other 😉

You could preprocess the values so the consecutive spaces are collapsed to a single one. There is no inbuilt function to do this (that I know) but you could create a map:

map:
MAPPING LOAD
Repeat(' ', RecNo()),
' '
Autogenerate 10; // 10 is the maximum number of consecutive spaces you can have, raise as needed

and then apply it using MapSubstring function:
MapSubstring('map', Var0)

You can either wrap this with individual Subfield() calls or do a initial load just for collapsing the spaces and do the Subfield in a preceding load or a resident load.

[Black_Hole]

Hi @kuba_michalik , @awaisqureshi ,

Thank you for your reply.

@awaisqureshi :  I put a sample in the attached file.

@kuba_michalik : I will try to see if I can resolve my problem in this way.

Thank you in advance for your help.

[awaisqureshi · Accepted solution]

Hi @Black_Hole ,

Here is @kuba_michalik's suggestion applied to your needs.

map:
Mapping
LOAD
Repeat(' ', RecNo()),
' '
Autogenerate 10; // 10 is the maximum number of consecutive spaces you can have, raise as needed


Table1:
LOAD
MapSubString('map', VOUCHER_TEXT) as Var0,
subfield(MapSubString('map', VOUCHER_TEXT),' ',1) as Var1,
subfield(MapSubString('map', VOUCHER_TEXT),' ',2) as Var2,
subfield(MapSubString('map', VOUCHER_TEXT),' ',3) as Var3
FROM [directory];

[Black_Hole]

Hello @kuba_michalik , @awaisqureshi ,

I tried the solution using the function Autogenerate 10 with my file and it's a great success !

Thanks @awaisqureshi  for taking the time to apply the @kuba_michalik's solution to my case.

Thank you very much for your help.