Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009

    Unanswered: Searching MEMO fields for specific terms

    I'm trying to develop a pass-thru query to extract the records from a table that includes a memo field.
    The text string I'm searching for is 'ILR = NO' and it's many variations (upper, lower & mixed case - no spaces or spaces included).
    To get round that I've used upper(memo_field) to force it all to upper case - and am searching for the occurrence of ILR and NO in separate statements so for example

    where upper(memo_field) like '%ILR%' and upper(memo_field) like '%NO%'

    it does return some records but not all of them (I've checked manually so I do know which records should come out in the category I sampled)

    Thinking it might be the field length that was causing a problem I've also tried

    where instr(upper(memo_field),'ILR') > 0 and substr(instr(upper(memo_field),'ILR'),10)

    to give me a subset of records and a shorter field to search in a subsequent access query - but it still doesn't bring out all the records...

    I get the impression that memo fields are a bit awkward - any comments/ideas will be gratefully received

    Our front end supplier calls it a 'memo field' so it kinda stuck - the field type is Varchar2, 2000 characters length
    Last edited by wobbleu; 02-11-11 at 10:44. Reason: clarification

  2. #2
    Join Date
    Jun 2003
    West Palm Beach, FL

    Thumbs down

    Memo fields?
    Not Oracle type.
    Haven't heard about memo fields since DBase IV.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    May 2004
    Dominican Republic
    So, give us some sample data to play with and show us a TEST CASE of your own where your search results are skipping records (sample data with create table, inserts, etc.).

  4. #4
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    Oracle Text could be useful. Maybe.

  5. #5
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Also check out REGEXP_LIKE() in the manual.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts