Solved

Please, help with PL/SQL programming

Posted on 2011-02-25
19
662 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server - Replace Carriage Returns for Excel Exports 17 51
sql help 8 55
TSQL query to generate xml 4 33
performance query 4 22
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

770 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