Comparing strings in Oracle

[drimades]

I have a field Website in the db table Authors declared as httpuritype. I need to compare the text within <title> ... </title> tag of that field with another field of a db table Works and select the records that have any word that appears within the tag <title> of the former field Website.

So if in the tag <title> there is the string "have a nice day" and in the field of the other table the string "a good day for a walk" it will return the record because they have any words matching. Any idea?

 

[SantaMufasa]

drimades,

Certainly, we can build a user-defined function in Oracle that:

1) accepts as function arguments two strings to compare.
2) separates out each word in each argument.
3) returns whatever you want if they have matches or returns whatever you want if they do not have matches.

My questions for you are:

1) besides spaces, what else could delimit words?
2) do you want matches of innocuous, "tiny" words such as "a", "an", "the", "in", "on", et cetera to trigger a match between the two strings?
3) what have you tried/designed so far to resolve your need?

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Dagon]

Here is a basic function which compares words. It assumes words are delimited by spaces:

CREATE OR REPLACE function compare_words (p_sentence1 in varchar2, p_sentence2 in varchar2) return varchar2 is
   v_pos1      number := 0;
   v_pos2      number;
   v_word1     varchar2(500);
   v_word2     varchar2(500);
   v_sentence1 varchar2(1000);
   v_sentence2 varchar2(1000);
begin
   if substr(p_sentence1, -1, 1) <> ' ' then
      v_sentence1 := p_sentence1 || ' ';
   else
      v_sentence1 := p_sentence1;
   end if;
   --
   if substr(p_sentence2, -1, 1) <> ' ' then
      v_sentence2 := p_sentence2 || ' ';
   else
      v_sentence2 := p_sentence2;
   end if;
   -- 
   loop
      v_word1 := substr(v_sentence1, v_pos1+1, instr(v_sentence1, ' ', v_pos1+1)-v_pos1);
      v_pos1 := instr(v_sentence1, ' ', v_pos1+1);
      exit when v_pos1 = 0;
	  v_pos2 := 0;
	  loop
         v_word2 := substr(v_sentence2, v_pos2+1, instr(v_sentence2, ' ', v_pos2+1)-v_pos2);
         if v_word2 = v_word1 then
            return 'Y';
         end if;
         v_pos2 := instr(v_sentence2, ' ', v_pos2+1);
         exit when v_pos2 = 0;
	  end loop;
   end loop;
   return 'N';
end;

select compare_words('one two three', 'four one nine') from dual

------------
'Y'

Other things you might have to consider, in addition to those mentioned by Dave, are the capitalisation of words and punctuation marks such as commas. Oracle 10 (which I don't have) has some additional features for regular expression handling which should allow things like looking for any whitespace character (e.g. tab, carriage return as well as space).

 

[drimades]

Thank you! I'll try it.

 

[drimades]

I have:

DECLARE
2 content CLOB;
3 uri httpuritype;
4 BEGIN
5 uri := httpuritype.createUri('[URL unfurl="true"]http://www.google.com');[/URL]
6 content := uri.getClob();
7 END;

the field of the table authors in this case is uri and is declared as httpuritype

how can I use HTML_SECTION_GROUP or something similar to section the html text that I have in content in the sections TITLE, HEADING, BODY, etc. so to perform CONTAINS queries like ... WHERE CONTAINS(TITLE, StringToFind) etc.?