Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have string from which I want to extract the 1st occurence of number. Let me explain with example
for example I have string AB-BC-A250B-B12C-AD .In this string you can see the 1st occurence of number is in part A250B i.e 250
Below are some more examples with output
Output
AB-BC-A250B-B12C-AD 250
AD-A2G0B-B12C-AD 20
BG-12-150A-BD 12
GB-A120-B130-CG 120
Please check if fits your needs
T:
MAPPING LOAD
chr(ord('0')+RecNo()-1) AS c,
'*'
AutoGenerate ORD('9') - ORD('0');
Data:
LOAD *,
KeepChar(SubField(Mid(Field,Index(t,'*')),'-',1),'0123456789') as finalfield;
LOAD
*,
MapSubString('T',Field) as t
Inline [
Field
AB-BC-A250B-B12C-AD
AD-A2G0B-B12C-AD
BG-12-150A-BD
GB-A120-B130-CG ];
drop Field t;
is there any possibility of any other string separator instead of '-'?
No, string will be separated by '-' only
Hi,
Check the example attached.
It may run slow on large and very large data sets.
Regards,
David
Data:
LOAD *,RowNo() as Row, KeepChar(subfield(Field,'-'),'0123456789') as Field1 Inline [
Field
AB-BC-A250B-B12C-AD
AD-A2G0B-B12C-AD
BG-12-150A-BD
GB-A120-B130-CG ];
Left Join
LOAD Field,
min(Row) as Row,
1 as MinRow
Resident Data
where len(trim(Field1))>0
Group by Field;
Final:
NoConcatenate
LOAD Field,
Field1 as Output
Resident Data
where MinRow=1;
DROP Table Data;
This is what you need:
AAA:
LOAD * Inline [
Campo, Out
AB-BC-A250B-B12C-AD, 250
AD-A2G0B-B12C-AD, 20
BG-12-150A-BD, 12
GB-A120-B130-CG, 120
];
BBB: NoConcatenate
LOAD Campo, Mid(Str2, 1, Len2-1) as String;
LOAD
Campo,
KeepChar(Campo, '1234567890-') as NewCampo,
FindOneOf(KeepChar(Campo, '1234567890-'),'1234567890') as Pos1,
Mid(KeepChar(Campo, '1234567890-'),FindOneOf(KeepChar(Campo, '1234567890-'),'1234567890')) as Str2,
Index(Mid(KeepChar(Campo, '1234567890-'),FindOneOf(KeepChar(Campo, '1234567890-'),'1234567890')),'-') as Len2
Resident AAA;
DROP Table AAA;
Please check if fits your needs
T:
MAPPING LOAD
chr(ord('0')+RecNo()-1) AS c,
'*'
AutoGenerate ORD('9') - ORD('0');
Data:
LOAD *,
KeepChar(SubField(Mid(Field,Index(t,'*')),'-',1),'0123456789') as finalfield;
LOAD
*,
MapSubString('T',Field) as t
Inline [
Field
AB-BC-A250B-B12C-AD
AD-A2G0B-B12C-AD
BG-12-150A-BD
GB-A120-B130-CG ];
drop Field t;
excellent clever
only one modification require . Your mapping table only maps numbers from 0 to 8.. there is possibility of number starting from 9. So I did below changes.. do let me know if did it correct
LOAD
chr(ord('0')+RecNo()-1) AS c,
'*'
AutoGenerate ORD('9') - ORD('0')+1;
Perfect, I was wrong