Link to home
Start Free TrialLog in
Avatar of for_yan
for_yanFlag for United States of America

asked on

Please, help with PL/SQL programming

Dear Experts,

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.




Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Please provide a little more information.

Must the 'allowed' letters appear in that order for the LIKE or in any order.

in other words is:  'GASPVTCLINDQKEMHFRYW' a valid entry and do you expect "STRIP(sequence_field) like '%PVTNDQ%'" to retunr the value?

I also assume that (g) is considered and 'invalid' value?  only '(p)' is allowed.

Some more sample data and expected results would help a lot.
Avatar of for_yan

ASKER

Sure, sorry I was not quite clear.

I want this function to go through the string in the sequence_field and remove all those
characters which are not present among letters GASPVTCLINDQKEMHFRYWgaspvtclindqkemhfrywXx
the order of the letteres is not important - e.g. sequence ISP LVW WLR  JRB should be transformed to
ISPLVWWLRR - so we should remove spaces and also remove J and B, because J and B are not present
among the specified letters in the string above. If there are other symbols in the sequence field, like "-","," or digits
or whatever, they all should also be removed, the only exception bieng special combination "(p)", so that

ISP LVW WLR  J(p)RB  should become ISPLVWWLR(p)R whereas combination  ISP LVW WLR  J(a)RB should become    ISP LVW WLRaR
because parentheses can be only retained within the "(p)" context. (this "(p)" has a special meaning
specifying that previous aminoacid (previous letter) was phosphorylated, therfore "(p)" will be retained intact,
and "(a)" should become "a", and say, "(b)" should be removed altogether, as letter "b" is not in the list).

Please, don't hesitate to let me know if you want further clarification.

Thanks a lot.




 


 

Avatar of for_yan

ASKER

Sorry I forgot to remove spaces in one of examples above (this is correct):

...whereas combination  ISP LVW WLR  J(a)RB should become    ISPLVWWLRaR ...
Man...  This will be tough in 9i.

Let me guess, upgrade to 10g is not an option?  This would be a LOT easier with regular expressions.

It's late for me here and I'm about done for the night.

Hopefully another Expert can pick this up but if not, I'll see what I can come up with tomorrow.

If you need it sooner, feel free to click the 'Request Attention' link above and see if a Moderator can find some Experts.
Avatar of for_yan

ASKER

No, upgrade is not currently in the plans.
But this is no urgency in terms of hours - tomorrow would also be great, I will wait - thanks a lot.
Hopefully some of our Experts in other time zones can come through.

Just to help them out, I thought of one last question:  like '%PVTNDQ%'

Those characters must occur in that order, correct?
Avatar of for_yan

ASKER


Yes in the query the order will be strict. People will want just to find all sequences containing specified subsequence in the specified order.
I've asked the Moderators to see if they can find some additional Experts to help because of your short timeframe.
Avatar of for_yan

ASKER

Thanks a lot, although it is not that urgent - if I get it next week it will still be awsome.
If character operations are difficult in PL/SQL, I can actually write this part in Java myself and we can make Java stored procedure, however,
I don't remember how to deploy it - did it once many years ago. So if I could get help in that parrt - it may be another option to consider.
Oops...  I mis-read the urgency.  I thought it was.

Sorry.
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of for_yan

ASKER

Great, so how should I get it in there.

I'm using normally sql through Toad.

So I can just paste it in Toad editor and make shift-f9 like excueting query in order to store the finction  in database?
yep

or F9 if it's the only thing in the editor window

or F5

Avatar of for_yan

ASKER

Wodnerful, let me try!
Avatar of for_yan

ASKER

Great, it works!

So if it worked through Toad, I can also use it in my java through JDBC - correct?
now that the function has been created you can call it in any sql or pl/sql you invoke, doesn't matter if you call the sql from toad, java, sql*plus, vb, perl, etc
Avatar of for_yan

ASKER

Thanks, guys! That was a great help.
It would have taken me days and pians to figure that out.
glad I could help!