Re: How to search for pattern on LONG columns

Prakash;

This might be a start for you. I found an message from Greg Scholey [RE: LONG to VARCHAR How can it be done} from 7/21/97 that helped me. I did a little mod for my specific needs...
(By the way - my thanks to Greg for his previous post !!)

FUNCTION -- lng2var(part_no)

This function will return with 255 characters of the part_comnt.comnt_txt field as a varchar2 datatype. To activate the function, pass the part_no (number datatype) to the function.

Because the comnt_txt column is stored as a long datatype, it cannot be used as part of a where clause in a select statement - some alternative method must be used. Don't know about ConTEXT option for Oracle - could be a possible solution as well.

EXAMPLE:
To return all rows of PART_NO, USER_PART_NO, PART_DESCRIPTION from PART where the PART_COMNT.COMNT_TXT is LIKE "some value"

select part_no, user_part_no, part_desc from part where lng2var(part_no) like '%SIM%';

• OR For Better Performance:

select p.part_no, p.user_part_no, p.part_desc, c.comnt_txt from part p, part_comnt c where lng2var(c.part_no) like '%SIMPLE%' and p.part_no = c.part_no;

SOURCE CODE: CREATE OR REPLACE function lng2var (vpart in number) return varchar2 is
result varchar2(255);
begin
select comnt_txt into result from part_comnt where part_no = vpart;
return result;
end;

Jim....
(Remove _no_at_spam_ from email !!)

Poorna Prakash wrote in message <34BE3B2D.25BA_at_sabre.com>...
>It appears impossible to me, to search for a pattern on a column whose
>datatype happens to be LONG. If someone could help me, suggest a way by
>which, I could convert LONG columns to VARCHAR2, or any other method by
>which you could approach this problem, I would greatly appreciate.
>
>Thanks much.
>
>Prakash
Received on Fri Jan 16 1998 - 00:00:00 CST