Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: string functions on LONG columns

    Setup: 9iDB

    I am working with a 3rd party app that is storing text in a LONG field. I need to select records where certain strings exist with in the text stored in the LONG field. i.e.

    Code:
    select column1, column2 from table
    where lower(long_column) like '%abc%'
    lower fails because it does not work with LONG. Any ideas how to get my result?
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    LONGs are a pig to work with, because virtually nothing works with them!

    PL/SQL can work with the first 32767 characters in a LONG, but you can't pass LONG values to/from functions in SQL. So one way would be to write a function like this:

    Code:
    create or replace function lower_long_column( p_pk in ... )
    return varchar2 is
      l_string varchar2(32767);
    begin
      select long_column
      into l_string
      from table
      where pk = p_pk;
      return lower(l_string);
    end;
    However, that will fail if the LONG's length exceeds 32767. In that case, you may need to convert the LONG to a CLOB before you can deal with it like this:
    Code:
    insert into table2 select to_lob(long_column) from table1;
    ... and then use the DBMS_LOB package on it.

  3. #3
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Thanks Tony. You are definitely "The Man".
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

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