Splitting Column based on a Character

Hello!

I'm interested in splitting a column that contains plate:well information that is delimited by a colon.  I just can't find a way to split it into two different columns (a plate column and a well column).

Any help with this would be greatly appreciated.

Hi, we don't currently have a way to do this directly in IJC (we hope to be adding this soon). But it can be done directly in the DB. If you can tell us what type of database you are uisng (local, oracle, mysql) then we can provide instructions.

Tim

Thanks for the quick reply!

It is a local database.

Hi

I have one question to ask about this : Is the ":" delimiter in a fixed position in the string or can it be variable?

The derby SUBSTR function can be used here but another function needs to be identified if the delimiter position is variable (the equivalent function required in Oracle is the INSTR function)

Many thanks,

Daniel.

Tim - has just pointed out that this is in fact the LOCATE function in derby - so does not matter if variable or not!

How do you employ those functions from the instant jchem interface?

Thank you very much for your time,

Joseph

I will provide an example on here ASAP which will show, essentially you need to run an UPDATE statement in derby against your table of interest. This will likely use the LOCATE and SUBSTR functions - you can run this statement either from IJC database explorer (part of netbeans) or connect using the ij tool and just run the update...

Here is what I did :

I administered a standard table in a local IJC project.Then, from the grid view of that table I added a new std field/column called 'plate_well' with a default value of 'plate:well'.

I then added a number of rows using the add row button. I then added two new std field/columns called 'plate' and 'well'.I then connected to the relevant derby database (you must disconnect from IJC project first) and checked the table contents(id and plate_well are populated).

I then ran two update statements below but you might wish to merge them into one.

update data set plate = substr(plate_well,1,locate(':',plate_well)-1);


update data set well = substr(plate_well,locate(':',plate_well)+1);

All columns are now popuated correctly as far as I can see.

It should not matter where in your string your delimiter is.

Cheers,

Daniel.

Just to be clear, you need to use the Database Explorer that is included in IJC to perform these direct operations on the database. This is a tool that is included with IJC, but is not really part of the IJC application. It lets you connect directly to the DB and perform operations directly on the database using SQL, which can be very useful sometimes. To find it show the Services window by using Window->Other->Services. Then the services window will open and you will see the 'Databases' node.

To connect to your database with the database explorer see the instructions described here:
https://www.chemaxon.com/instantjchem/ijc_latest/docs/user/help/htmlfiles/tips_and_tricks/db_explorer.html

Then, once you have a connection you can execute the SQL as described in the previous post.

When finished you can disconnect the DB explorer and reconnect from IJC and you should see the results.

Tim

Thank you for all the replies!

I got as far as entering the sql command.  However when I do I get this error message:

Error code 30000, SQL state 42X05: Table/View 'DATA' does not exist.

Line 1, column 1

Execution finished after 0 s, 1 error(s) occurred.

--------------

What do you guys think?  My columns are varchar data types.  And when I select "view data" while connected it gives me all the data that was originally present.  However when I do view the data it runs the command:

select PLATE_WELL from APP.COMPOUNDS_COMPLETE

Would this have anything to do with it?

Yes, you need to replace DATA with the actual name of the table you are using e.g. COMPOUNDS_COMPLETE (and posibly the column names if you are not using the ones we assumed).

Tim

Thank you very much!  It works perfectly now.