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!

Search for Word in database column

Discussion in 'SQL PL/SQL' started by fullyii, Dec 19, 2012.

  1. fullyii

    fullyii Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I am attempting to search for a word in a field. The field is a blob field and I want to display the next 8 characters after the word is found.

    This is what I have so far:

    REGEXP_SUBSTR(blob to clob(ML.BODY), 'COURSE',0,8)


    Any help would be greatly appreciated
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If the field is truly a BLOB you won't have much luck as the contents are binary; a CLOB would be searchable.

    Code (SQL):
    SQL> SELECT dbms_lob.substr(mytext, dbms_lob.instr(mytext, 'ONE'), 8)
      2  FROM mylob
      3  WHERE dbms_lob.instr(mytext, 'ONE') > 0;
     
    DBMS_LOB.SUBSTR(MYTEXT,DBMS_LOB.INSTR(MYTEXT,'ONE'),8)
    --------------------------------------------------------------------------
    ONE
    TWENTY-ONE
    THIRTY-ONE
    FORTY-ONE
    FIFTY-ONE
    SIXTY-ONE
    SEVENTY-ONE
    EIGHTY-ONE
    NINETY-ONE
    ONE HUND
     
    10 ROWS selected.
     
    SQL>
    You could search a BLOB for a RAW value but searching for plaintext in a BLOB is wasted effort.
     
    Hamza Bukhari likes this.
  3. fullyii

    fullyii Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    The field is a Blob although I am converting it to a clob. Within each string I want to find the word and then only display the 8 characters of data after the word.

    If I understand your solution it will only display the found string or a variation of that found string.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, it displays the 8 characters after the start of that string, which is what it appeared you were asking to return. Now that you have clarified what it is you want:

    Code (SQL):
    SQL> CREATE TABLE mylob (myid NUMBER NOT NULL,
      2          mytext CLOB);
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..100 loop
      3                  INSERT INTO mylob
      4                  VALUES(i, 'Text:  '||to_char(to_date(i, 'J'), 'JSP')||' and a partridge in a pear tree');
      5          END loop;
      6
      7          commit;
      8
      9  END;
     10  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT substr(mytext, instr(mytext, 'ONE') + 3, 8)
      2  FROM mylob
      3  WHERE instr(mytext,'ONE') >0;
     
    SUBSTR(MYTEXT,INSTR(MYTEXT,'ONE')+3,8)
    --------------------------------------------------------------------------------
     AND a p
     AND a p
     AND a p
     AND a p
     AND a p
     AND a p
     AND a p
     AND a p
     AND a p
     HUNDRED
     
    10 ROWS selected.
     
    SQL>
    Notice you can also use the 'plain vanilla' INSTR and SUBSTR functions to return the data from a CLOB with better results.
     
  5. fullyii

    fullyii Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thank you very much - This is what i needed.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please note that the 'adjustment' depends on the length of the string you're searching for -- my string was 3 characters long. Adjust the query accordingly.