0

I've got example two strings with the following layout:

'COMPANY A - 01-Jan-19 - 30-Jun-19 - 100.00%'
'COMPANY B - 01-Jan-19 - 30-Jun-19 - 50.00% / COMPANY C - 01-Jan-19 - 30-Jun-19 - 50.00%'.

It describes the percentage of a consultant who works at a certain client or multiple clients. I'm receiving their info in the above layout. What I would like to do is to split those strings and store it in a table. The structure of the table is as follows (with the intentional data between brackets):

  • ID - Number --Automatic generated row ID
  • ConsultantID - Number --Foreign key to Person table
  • Company - Varchar2(128) --Name of the company where the consultant works (Company A)
  • StartDate - Date --Start date of the project (01-Jan-19)
  • EndDate - Date --End date of the project (30-Jun-19)
  • Percentage - Number --Percentage of time consultant works at the client (100.00%)

As far as I've seen so far the max number of companies someone works for is two, but might be more.

I'm working in Oracle SQL and that's non negotiable with the project I'm working with.

I've started with PL/SQL not that long ago (I assume I need PL/SQL for this, because I can't figure out a way to do this only with Oracle SQL) and I haven't figured out a way to make this work. The splitting isn't the problem when there are one (obvious) or two different companies, but if there are more than two I don't know how to automate that. With languages like Java it wouldn't be a problem with array's and a for loop, but in PL/SQL for me it is still problem...

Below some code that I'm using for the splitting.

DECLARE
total varchar2(256) := 'Company B - 01-Jan-19 - 30-Jun-19 - 50.00% / Company C - 01-Jan-19 - 30-Jun-19 - 50.00%';
firstPart varchar2(128);
secondPart varchar2(128);
BEGIN   

firstPart := SUBSTR(total, 0, (INSTR(total, '/')) -2);
secondPart := SUBSTR(total, (INSTR(total, '/')) +2);
END;

Is there a way to make this work with (potentially) infinite companies? If it's very difficult I can make an assumption that people work at no more than five companies, but I very much would like to have no maximum.

The second part is to split company name, start date, end date and percentage. I don't think it's that hard, but it might be relevant for the solution to the main problem. So if you have a solution for the main problem I can pick it up from there.

Feel free to ask any questions or to make me feel stupid because it's easy and I just haven't found the solution yet.

Thanks a lot in advance!

APC
  • 144,005
  • 19
  • 170
  • 281
Snake
  • 283
  • 4
  • 10
  • 1
    In a world which contains XML, JSON, CSV files etc and has done for many years, why do people still insist on coming up with their own non-standard ways of passing concatenated values in strings? I know it's the requirement you're working to, but the sheer amount of developer effort that is wasted on tasks like this makes me weep. – APC Feb 11 '20 at 15:59
  • 1
    Anyway, there are [already plenty of questions on splitting strings in Oracle](https://stackoverflow.com/search?q=oracle+split+string). Why not look at some of them? Because you have a complicated format you should consider writing [a pipelined function like this](https://stackoverflow.com/a/3710619/146325) rather than wrestling with convoluted regex solutions. – APC Feb 11 '20 at 16:02
  • 1
    Java has some very powerful tools for parsing strings. You could create a class in the database, and use something like split(). If you are interested in such an approach, I could provide an example. – Acroyear Feb 11 '20 at 16:34
  • 1
    Oracle has them too. [dbfiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=dcc5814ccc131ac864e55160e9c4c3e5). SQL and PL/SQL version. – Ponder Stibbons Feb 11 '20 at 16:44
  • @APC Thank you for your reply. I know it's bad practice, but I only get an Excel file with a lot of things in separate columns, but this they parse together for some reason. And I don't have any influence on the format of the Excel file. I've complained about it too, but it didn't help. Thanks for the advice, I'm gonna look into them. Couldn't find any questions myself, but thanks for the help. – Snake Feb 12 '20 at 13:30
  • @Acroyear Thanks for the tip, but using Java is not an option, otherwise it would be a good solution I think. – Snake Feb 12 '20 at 13:31
  • @PonderStibbons Thanks for the tip! I'm gonna look into it – Snake Feb 12 '20 at 13:32

0 Answers0