Solved

Please, help with PL/SQL programming

Posted on 2011-02-25
19
660 Views
Last Modified: 2013-12-19
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.




0
Comment
Question by:for_yan
  • 9
  • 5
  • 4
19 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34985489
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.
0
 
LVL 47

Author Comment

by:for_yan
ID: 34985548
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.




 


 

0
 
LVL 47

Author Comment

by:for_yan
ID: 34985553
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 ...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34985578
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.
0
 
LVL 47

Author Comment

by:for_yan
ID: 34985598
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34985607
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?
0
 
LVL 47

Author Comment

by:for_yan
ID: 34985621

Yes in the query the order will be strict. People will want just to find all sequences containing specified subsequence in the specified order.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34985656
I've asked the Moderators to see if they can find some additional Experts to help because of your short timeframe.
0
 
LVL 47

Author Comment

by:for_yan
ID: 34985671
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.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34985677
Oops...  I mis-read the urgency.  I thought it was.

Sorry.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 34985770
I believe this will do it...
CREATE OR REPLACE FUNCTION strip(p_string VARCHAR2)
    RETURN VARCHAR2
AS
    c_keep   VARCHAR2(4001)
                 := 'GASPVTCLINDQKEMHFRYWgaspvtclindqkemhfryw' || 'X';

    v_length NUMBER := LENGTH(p_string);
    v_start  NUMBER := 1;
    v_index  NUMBER;
    v_result VARCHAR2(32767);
BEGIN
    WHILE (v_start <= v_length)
    LOOP
        v_index := INSTR(p_string, '(p)', v_start);

        IF v_index = 0
        THEN
            v_result :=
                v_result
                || TRANSLATE(
                       SUBSTR(p_string, v_start),
                       c_keep
                       || TRANSLATE(p_string, CHR(0) || c_keep, CHR(0)),
                       c_keep
                   );
            v_start := v_length + 1;
        ELSE
            v_result :=
                v_result
                || TRANSLATE(
                       SUBSTR(p_string, v_start, v_index - v_start),
                       c_keep
                       || TRANSLATE(p_string, CHR(0) || c_keep, CHR(0)),
                       c_keep
                   )
                || '(p)';
            v_start := v_index + 3;
        END IF;
    END LOOP;

    RETURN v_result;
END strip;

Open in new window

0
 
LVL 47

Author Comment

by:for_yan
ID: 34985776
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?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34985781
yep

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

or F5

0
 
LVL 47

Author Comment

by:for_yan
ID: 34985785
Wodnerful, let me try!
0
 
LVL 47

Author Comment

by:for_yan
ID: 34985801
Great, it works!

So if it worked through Toad, I can also use it in my java through JDBC - correct?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34985806
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
0
 
LVL 47

Author Comment

by:for_yan
ID: 34985807
Thanks, guys! That was a great help.
It would have taken me days and pians to figure that out.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34985826
glad I could help!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now