AnsweredAssumed Answered

Split a data item into two for all records in a field

Question asked by m.swanston on Feb 25, 2016
Latest reply on Feb 26, 2016 by m.swanston

So I'm still a relative novice with FM - I support a tool written by an external developer and so just fix bugs and try to add/improve functionality. One of the changes I've been asked to look at it is to break up a 'column' of data into two (where possible). When the developer was here, I am sure I watched him do something similar, but can't for the life of me remember what/how he did it.

So the data looks like this (but for over a thousand records):

d_Oracle_Code

3058382

3055566 04.13

3052494.04.13

3057982 04.13

3057982 04.01

3057982 04.13

3057982 04.13

3056564

3023607.04.13 (US)

3052095 04 25

...

So as you can see, there are some records that only contain a seven digit number, and others that contain both the seven digit number and the appropriate task code, but either delimited by a space or a full stop/period. I have created a new field called d_Task_Code, and I would like to put the 5 digit task code into the new field and leave only the 7 digit code behind (removing any delimiter), so it would look something like this:

d_Oracle_Code     d_Task_Code

3058382

3055566                04.13

3052494                04.13

3057982                04.13

3057982                04.01

3057982                04.13

3057982                04.13

3056564

3023607               04.13 (US)

3052095               04 25

...

Is it possible to write something that would apply this split to all records? For the few that are incorrect ie the last one in the example, would it be possible to insert a full stop/period as well?

Thanks in advance

Martin

Outcomes