How to load data only between first "_" marks

[Anonymous]

Hello,

Just a simple question I didn't figured out by myself.

I have data that has a column [File_name] with data like:

AB_0008417891_XXYY1410_30.11.2015_20151201125515_151002228001.PDF

BC_44028549_AA_S.afp

How do I read only data between first "_" marks? I want my loaded data look like:

0008417891

44028549

Thanks for help.

- Risto

[sunny_talwar · Accepted solution]

May be try SubField() function:

SubField(File_name, '_', 2)

Table:

LOAD *,

  SubField(File_name, '_', 2) as NewName;

LOAD * Inline [

File_name

AB_0008417891_XXYY1410_30.11.2015_20151201125515_151002228001.PDF

BC_44028549_AA_S.afp

];

You can also use

TextBetween(File_name, '_', '_', 1) as NewName2

[its_anandrjs]

Then write

=SubField([File_name],'_',1)

it gives

AB

BC

And

=SubField([File_name],'_',2)

it gives

0008417891

44028549

And

=Left([File_name],2)

Check this Example

LOAD *,SubField(Test,'_',1) as Field1,
       SubField(Test,'_',2) as Field2,
       Left(Test,2) as Field3;

LOAD * Inline
[
Test
AB_0008417891_XXYY1410_30.11.2015_20151201125515_151002228001.PDF
BC_44028549_AA_S.afp
];

Regards

Anand

[Anonymous]

Thanks Sunny T!

I used that TextBetween function. Works flawlessly!

- Risto