Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Thanks Sunny T!
I used that TextBetween function. Works flawlessly!
- Risto