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!

locate an occurrence of symbols combination

Discussion in 'SQL PL/SQL' started by ecivgamer, Aug 5, 2011.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all,

    My need is to locate an occurrence of symbols starting from "s." (non-capital letter), following by word (with any capital letter at the beginning) and ending with ", " (comma and space symbols).

    Ex:

    I'm looking for occurrence of "s.Glkgje, " and "s.Tklgj, ".

    I think some combination of REGEXP_INSTR and REGEXP_SUBSTR should be helpful, but I'm not familiar with these functions so good yet.
    Please suggest be the right syntax.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    SQL> CREATE TABLE testit(
    2 srch_strg varchar2(100)
    3 );
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
    2 FOR i IN 1..3 loop
    3 INSERT INTO testit
    4 SELECT 'jeklghje, s.Glkgje, u.slgjwek, 904869, '||i AS tt FROM dual UNION ALL
    5 SELECT 's.Tklgj, u.slgjwek, 23578, elslgjs, '||i AS tt FROM dual UNION ALL
    6 SELECT 's.klgj, u.ekgjes, 238573, dlsjkgj, '||i AS tt FROM dual ;
    7 END loop;
    8
    9 commit;
    10
    11 END;
    12 /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT srch_strg FROM testit;
     
    SRCH_STRG
    ----------------------------------------------------------------------------------------------------
    jeklghje, s.Glkgje, u.slgjwek, 904869, 1
    s.Tklgj, u.slgjwek, 23578, elslgjs, 1
    s.klgj, u.ekgjes, 238573, dlsjkgj, 1
    jeklghje, s.Glkgje, u.slgjwek, 904869, 2
    s.Tklgj, u.slgjwek, 23578, elslgjs, 2
    s.klgj, u.ekgjes, 238573, dlsjkgj, 2
    jeklghje, s.Glkgje, u.slgjwek, 904869, 3
    s.Tklgj, u.slgjwek, 23578, elslgjs, 3
    s.klgj, u.ekgjes, 238573, dlsjkgj, 3
     
    9 ROWS selected.
     
    SQL>
    SQL> SELECT substr(srch_strg, regexp_instr(srch_strg, '[s\.][A-Z][a-z]*[,]',1,1,0,'c')-1, instr(srch_strg, ',', (regexp_instr(srch_strg, '[s\.][A-Z][a-z]*[,]',1,1,0,'c')-1), 1) - (regexp_instr(srch_strg, '[s\.][A-Z][a-z]*[,]',1,1,0,'c')-2))
    2 FROM testit
    3 WHERE regexp_instr(srch_strg, '[s\.][A-Z][a-z]*[,]',1,1,0,'c')-1 > 0;
     
    SUBSTR(SRCH_STRG,REGEXP_INSTR(SRCH_STRG,'[S\.][A-Z][A-Z]*[,]',1,1,0,'C')-1,INSTR(SRCH_STRG,',',(REGEXP_INSTR(SRCH_STRG,'[S\.][A-Z][A-Z]*[,]',1,1,0,'C'
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    s.Glkgje,
    s.Tklgj,
    s.Glkgje,
    s.Tklgj,
    s.Glkgje,
    s.Tklgj,
     
    6 ROWS selected.
     
    SQL>