Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Given below is a sample of data loaded under CREDITOR_CODE which are having varying length as I have counted in excel. I have shown only a part of data & the list contains more than 100 records with different length in each row. Can our expert propose an expression to extract codes & names separately?
Thanks neville
CREDITOR _CODE | LEN | ||
CREDITOR CODE 127524 M.P.PATHIRANA | 37 | ||
CREDITOR 127454 S.SIVAKUMARA | 30 | ||
CREDITOR CODE 52514-011 SEYLAN BANK | 35 | ||
CREDITOR CODE 145214_125 COMMERCAL BANK | 40 | ||
CREDITOR 1452142 COMMERCCAI CREDIT | 36 | ||
CREDITOR 124114-011 PAN ASIA BANK | 34 | ||
I need to extract all numbers given in each row as creditor codes | |||
I need to extract the creditor names which are on the right side of each row | |||
Another option
Base:
LOAD *,
Mid(CREDITOR_CODE, FindOneOf(CREDITOR_CODE, '0123456789')) as [creditor names];
LOAD * INLINE [
CREDITOR_CODE, LEN,
CREDITOR CODE 127524 M.P.PATHIRANA, 37,
CREDITOR 127454 S.SIVAKUMARA, 30,
CREDITOR CODE 52514-011 SEYLAN BANK, 35,
CREDITOR CODE 145214_125 COMMERCAL BANK, 40,
CREDITOR 1452142 COMMERCCAI CREDIT, 36,
CREDITOR 124114-011 PAN ASIA BANK, 34
];
Please try this
Base:
LOAD * INLINE [
CREDITOR_CODE, LEN,
CREDITOR CODE 127524 M.P.PATHIRANA, 37,
CREDITOR 127454 S.SIVAKUMARA, 30,
CREDITOR CODE 52514-011 SEYLAN BANK, 35,
CREDITOR CODE 145214_125 COMMERCAL BANK, 40,
CREDITOR 1452142 COMMERCCAI CREDIT, 36,
CREDITOR 124114-011 PAN ASIA BANK, 34
];
load
CREDITOR_CODE as FULL_LINE,
[creditor codes],
Trim(Mid(CREDITOR_CODE,Index(CREDITOR_CODE,[creditor codes])+Len([creditor codes]))) as [creditor names];
load
CREDITOR_CODE,
KeepChar(CREDITOR_CODE,'0123456789-_') as [creditor codes]
Resident Base;
Drop Table Base;
Another option
Base:
LOAD *,
Mid(CREDITOR_CODE, FindOneOf(CREDITOR_CODE, '0123456789')) as [creditor names];
LOAD * INLINE [
CREDITOR_CODE, LEN,
CREDITOR CODE 127524 M.P.PATHIRANA, 37,
CREDITOR 127454 S.SIVAKUMARA, 30,
CREDITOR CODE 52514-011 SEYLAN BANK, 35,
CREDITOR CODE 145214_125 COMMERCAL BANK, 40,
CREDITOR 1452142 COMMERCCAI CREDIT, 36,
CREDITOR 124114-011 PAN ASIA BANK, 34
];