remove employee id value appended to name

[#1]

Our source database has our employee name & ID in the same field, for example "John Smith 123456"

I want to remove the id part and just show "John Smith" etc

Obviously the character length of each name changes but they are always separated by a space, so I'd need to remove the characters after the second space!

I'd like to do this in the edit script, and created as a new filed in the load, so the original version is still available

What's best way??

Thanks

[Peter_Cammaert · Accepted solution]

If the IDs are strictly numerical (only digits) and your employee names do not contain any digits, your can use something like

... trim(PurgeChar([Employee Field], '0123456789')) AS EmployeeName, ...

Peter

[fvelascog72]

Hi,

If ID length is always the same (n), you can use right(FIELD,n) as ID

and left(FIELD,(len(FIELD)-(n+1)) as Employee

[#4]

Perfect thank you Peter

[#5]

if you want to base your derivation on a space you can try this:

mid([Employee Field],index([Employee Field],' '))

purgeChar might cause an issue if the id is alpha numeric. if it is not the case then purgechar is good to go.

Also if you decide you want the ID as a column and it is always numeric, you could use the keep char.

KeepChar([Employee Field], '0123456789') AS EmployeeID