StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMatch words of string column in a table with words of string in another column oracle
text
Body
copied!<p>I have two tables for example take two small input tables:- </p> <pre><code>Table1:- columnA man got wounded by dog joe met sally Table2:- ColumnB life is good dog man got hunt dumb man wounded iron </code></pre> <p>I want to search for a row in columnA the row from columnB with maximum matching words for Eg:-</p> <pre><code>Intermediate Output of above table should be:- ColumnA ColumnB words_matching number_of_words "man got wounded by dog" "dumb man wounded iron" "man,wounded" 2 "man got wounded by dog" "dog man got hunt" "dog,man,got" 3 </code></pre> <p>In final result output i want to show:-</p> <pre><code>ColumnA ColumnB words_matching number_of_words "man got wounded by dog" "dog man got hunt" "dog,man,got" 3 </code></pre> <p>P.S:- I have provided the output for only one case,the table will be huge. Also couldn't add spaces between column data so used quotes.</p> <p>I have tried doing the above my breaking the string using heirarchial query but it takes a lot time:- example of how i break the string:- </p> <pre><code>select column1,regexp_substr(column1,'[^ ]+', 1, level) break_1 from table1 connect by regexp_substr(column1,'[^ ]+', 1, level) is not null; </code></pre> <p>Below is another query i came up with but don't think its a good idea for huge data as the performance is very low due to cartesian join:</p> <pre><code> select st1,st2, max(round((extractvalue(dbms_xmlgen.getxmltype('select cardinality ( sys.dbms_debug_vc2coll(''' || replace(replace(lower(st1),''''), ' ', ''',''' ) || ''') multiset intersect sys.dbms_debug_vc2coll('''||replace(replace(lower(st2),''''), ' ', ''',''' )||''')) x from dual'), '//text()')),2)) seq from ( select l1.column1 st1,l2.column2 st2 from table1 l1,table2 l2 ) group by st1,st2; </code></pre> <p>Can someone suggest a good approach--</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload