String Functions

[akpofureenughwu]

I have a field that contains staff names and ID. I would love to seperate them so I can have a column for name and another for id...

I used the right('ID', 5) as IDNO.... but it is not giving the desired format...

Please script below and attached picture

LIB CONNECT TO 'UGOCHI HR';

LOAD ID,

     Right( ID, 5) as IDNO,

    "NAME_OF_EMPLOYEE",

    DEPARTMENT,

    "LOAN_START_DATE",

    "REPAYMENT_START_DATE",

    "EXPECTED_LOAN_END_DATE",

    "LOAN_AMOUNT_GRANTED",

    "INSTALLMENT_AMOUNT",

    "LOAN_AMOUNT_PAID",

    "OUTSTANDING_BALANCE",

''''''''''''''''''''''''''''''''...............................................

[shivkumar300]

you can mention the below Right function and mention the number accordingly.Please find the below example

data:
LOAD* inline [
ID
AAAA12333
AAAA12334
AAAA12335
AAAA12336
AAAA12337
AAAA12338
AAAA12339
AAAA12340
AAAA12341
AAAA12342
AAAA12343
AAAA12344
];

dat1:
load right ("ID",5) as IDNo   ----> use this

Resident data;
DROP Table data;

[rahulpawarb]

Hello Akpofure,

Trust that you are doing great!

Please refer below sample script:

Data:
LOAD
ID,
REPLACE(ID, RIGHT(ID,5), ' ') AS StaffName,
RIGHT(ID,5) AS StaffId;
LOAD * INLINE [
ID
Abanum Odikayor19145
Abayomi Daniel90536
Abdel Mohammed18235
Abdulgafar Zakariyau18479
Abdulkadir Yusuf20638
Abdulkarim Braimoh74235
];

Hope this will be helpful.

If you are still facing challenges then please share the application with sample data.

P.S.: This script is designed considering StaffId is of 5 character length.

Regards!

Rahul

[sunny_talwar · Accepted solution]

May be try one of these:

Right(Trim("ID"), 5) as IDNO

or

KeepChar("ID", '1234567890') as IDNO