Extract substring of values based upon the different delimeter

[Anonymous]

Hi All,

I have values like as below.

Description

----------------

106:Undef

1379.UpperProvidence

Aloha Channel 1378.5250

I need to extract the ID and their description separately from above data set.

Expected Result:

ID   |  Description

-------------------------

106      Undef

1379    UpperProvidence

5250    Aloha Channel 1378

How we can achieve this? Can anyone give me the suggestion for that.

If similar kind of data set we can achieve by using subfield().

Regards,

Shakila Desamuthu

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

table1:
LOAD If(Index(Description,':'),SubField(Description,':',1),If(Index(Description,'.'),If(IsNum(SubField(Description,'.',1)),SubField(Description,'.',1),SubField(Description,'.',2)))) as ID,
    If(Index(Description,':'),SubField(Description,':',2),If(Index(Description,'.'),If(IsNum(SubField(Description,'.',1)),SubField(Description,'.',2),SubField(Description,'.',1)))) as Description
INLINE [
    Description
    106:Undef
    1379.UpperProvidence
    Aloha Channel 1378.5250
];

hope this helps

regards

Marco

[Kushal_Chawda]

another way could be

Map:

mapping LOAD * Inline [

TextFrom, TextTo

:,-

.,- ];

table1:

LOAD *, if(isnum(SubField(DescriptionNew,'-',1)),SubField(DescriptionNew,'-',1),if(isnum(SubField(DescriptionNew,'-',2)),SubField(DescriptionNew,'-',2))) as ID,

              if(IsText(SubField(DescriptionNew,'-',1)),SubField(DescriptionNew,'-',1),if(IsText(SubField(DescriptionNew,'-',2)),SubField(DescriptionNew,'-',2))) as DescriptionFinal;

LOAD * , MapSubString('Map',Description) as DescriptionNew

INLINE [

    Description

    106:Undef

    1379.UpperProvidence

    Aloha Channel 1378.5250

];

[Anonymous]

Thanks, its working fine.

[Anonymous]

Hey, this logic too satisfying. Thanks for the update.