[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 668
  • Last Modified:

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.




0
for_yan
Asked:
for_yan
  • 9
  • 5
  • 4
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
for_yanAuthor Commented:
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
 
for_yanAuthor Commented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
slightwv (䄆 Netminder) Commented:
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
 
for_yanAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
for_yanAuthor Commented:

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
 
slightwv (䄆 Netminder) Commented:
I've asked the Moderators to see if they can find some additional Experts to help because of your short timeframe.
0
 
for_yanAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
Oops...  I mis-read the urgency.  I thought it was.

Sorry.
0
 
sdstuberCommented:
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
 
for_yanAuthor Commented:
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
 
sdstuberCommented:
yep

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

or F5

0
 
for_yanAuthor Commented:
Wodnerful, let me try!
0
 
for_yanAuthor Commented:
Great, it works!

So if it worked through Toad, I can also use it in my java through JDBC - correct?
0
 
sdstuberCommented:
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
 
for_yanAuthor Commented:
Thanks, guys! That was a great help.
It would have taken me days and pians to figure that out.
0
 
sdstuberCommented:
glad I could help!
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 9
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now