1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

string comparison issue reg

Discussion in 'SQL PL/SQL' started by laxman, Feb 19, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear all,
    In pl/sql block i have written two query as mentioned below.


    declare
    L_textmailA varchar2(40);
    L_textmailB varchar2(40);
    begin
    --- from table A
    select textmail into L_textmailA from A where mailid=1020;

    -- from table B
    select textmail into L_textmailB from B where mailid= 1224;


    end;

    Now inside this code i want to campare L_textmailA and L_textmailB and if they match i need to update some other table based on current requirement.

    Can anybody help me out from this issue

    Thanks N regrads
    Laxman
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Such a task is fairly easy:

    Code (SQL):
    IF L_textmailA = L_textmailB THEN
        UPDATE ...
    END IF;
    This presumes an exact match including the character case; any difference will cause the comparison to fail.
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    thanks a lot sir, that means if i take a case of word by word comparison for both textmail i can apply this logic?. actually somebody suggest me to apply dcode functions or REGEXP but i was not sure ,anyway sir i will implement by applying this method.

    Regards
    Laxman
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    to compare and eliminate case sensitive ,if needed, we can use to_upper or to_lower functins

    Code (SQL):
    ex:
    IF to_upper(L_textmailA) = to_upper(L_textmailB) THEN
    ---code;
    END IF;
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This Can be done in a using asingle statement also as shown below.

    Code (SQL):

    UPDATE TABNAME
    SET COL1 = ????
    WHERE EXISTS ( SELECT NULL
                   FROM A
                   WHERE MAILID=1020
                   AND TEXTMAIL  =(SELECT TEXTMAIL  
                                   FROM B
                                   WHERE MAILID= 1224))
     
    You can use your required function also inside the query..
     
  6. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Yes this is nice one
     
  7. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    thank u very much sir its working fine.
     
  8. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    thanks kiran for ur valuable information