Appendix - Design and Use of Relational Databases in Chemistry - page 209

select id, (p).name, (p).value from
(select id, molfile_properties(molfile) as p from $schema.sdf)
atmp;

-- This regexp may not catch all numeric values
Update $schema.property Set nvalue = tvalue::numeric
Where tvalue ~ E'^[+-]?[0-9]+(\\\\.[0-9]*)?([Ee][+-]?[0-9]+)?\$';
-- You may choose to name colums to be converted to numeric
--Update $schema.property Set nvalue = tvalue::numeric
-- Where Name = 'IC50_uM';
EOSQL

This script can be used as follows.

perl sdfloader vla4 <vla-4.sdf | psql mydb

There are many sources of sample structure files. The examples used
in this topic come from the pubchem project 8 and the QSAR world proj-
ect. 9 There are SMILES files available from the National Cancer Institute
of the National Institutes of Health. 10
The drugs.smi file is used in several places in the previous chapters.
This file contains the SMILES shown in Table A.5.

Table A.5 SMILES and Names Used in Sample Table Named Drugs

CN1C2CCC1CC(C2)OC(=O)C(CO)c3ccccc3 atropine

c1cc(ccc1C(C(CO)NC(=O)C(Cl)Cl)O)N(=O)=O chloramphenicol

c1c2c(cc(c1Cl)S(=O)(=O)N)S(=O)(=O)N=CN2 chlorothiazide

CN(C)CCCN1c2ccccc2Sc3c1cc(cc3)Cl chlorpromazine

Cc1c(nc[nH]1)CSCCNC(=NC#N)NC cimetidine

CN1c2ccc(cc2C(=NCC1=O)c3ccccc3)Cl diazepam

CC(=O)OC1C(Sc2ccccc2N(C1=O)CCN(C)C)c3ccc(cc3)OC diltiazem

CN(C)CCOC(c1ccccc1)c2ccccc2 diphenhydramine

c1ccc(c(c1)C(=O)O)Nc2cccc(c2)C(F)(F)F flufenamic acid

c1cc(ccc1C(=O)CCCN2CCC(CC2)(c3ccc(cc3)Cl)O)F haloperidol

CN(C)CCCN1c2ccccc2CCc3c1cccc3 imipramine

CCN(CC)CC(=O)Nc1c(cccc1C)C lidocaine

CCC1(C(=O)NC(=O)NC1=O)c2ccccc2 phenobarbital

c1ccc(cc1)C2(C(=O)NC(=O)N2)c3ccccc3 phenytoin

CCN(CC)CCNC(=O)c1ccc(cc1)N procainamide

CC(C)NCC(COc1cccc2c1cccc2)O propranolol

CCCCNc1ccc(cc1)C(=O)OCCN(C)C tetracaine

COc1cc(cc(c1OC)OC)Cc2cnc(nc2N)N trimethoprim

CC(C)C(CCCN(C)CCc1ccc(c(c1)OC)OC)(C#N)c2ccc(c(c2)OC)OC verapamil