Please, help with PL/SQL programming
Posted on 2011-02-25
I use a lot of SQL in my work and know a little bit of PL/SQL, but I was doing PL/SQL programming
so rarely, that each time I need it, it becomes a big problem
for me, so I was thinking maybe someone could be so kind as to help me with a little bit of PL/SQL programming.
We have an Oracle table, in one of the fields of which we store peptide sequence - a sequence of upper and lower case
letters out of this set: GASPVTCLINDQKEMHFRYWgaspvtclindqkemhfryw (in addition valid letters are letter "X" and combination "(p)" - a
modifier after some of the peptide letters).
However the sequences are stored as they were originally input by the user - sometimes with dashes, sometimes
with spaces or even other extra symbols. I'd like to have function, say, STRIP which would
strip away all extra symbols and allow me to search this table using subsequence, something like that:
select * from my_table where STRIP(sequence_field) like '%PVTNDQ%'
If you help me with this code and guide me with deploying it in the database, I'd be very thankful.
The database is Oracle 9 on Solaris, the table is small - about couple of thousand lines, so
performance is not that big of an issue
Thanks a lot.