for_yan
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: GASPVTCLINDQKEMHFRYWgaspvt clindqkemh fryw (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.
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: GASPVTCLINDQKEMHFRYWgaspvt
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.
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 GASPVTCLINDQKEMHFRYWgaspvt clindqkemh frywXx
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.
I want this function to go through the string in the sequence_field and remove all those
characters which are not present among letters GASPVTCLINDQKEMHFRYWgaspvt
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.
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 ...
...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.
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.
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.
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?
Just to help them out, I thought of one last question: like '%PVTNDQ%'
Those characters must occur in that order, correct?
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.
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.
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.
Sorry.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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
or F9 if it's the only thing in the editor window
or F5
ASKER
Wodnerful, let me try!
ASKER
Great, it works!
So if it worked through Toad, I can also use it in my java through JDBC - correct?
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
ASKER
Thanks, guys! That was a great help.
It would have taken me days and pians to figure that out.
It would have taken me days and pians to figure that out.
glad I could help!
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.