Phil, you can do it with SQL, but it gets kind of ugly. SELECT EXTRACTVALUE (VALUE (d), '/g') AS rslt FROM TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE.createxml ( '<row><g>' || REPLACE ('car was red and next to a crimson motor', ' ', '</g><g>' ) || '</g></row>' ), '/row/g' ) ) ) d;
RSLT -------------------------------------------------------------------------------- car was red and next to a crimson motor 9 rows selected. If this is something going into a production environment, I would use PL/SQL, simply for debugging reasons. Mike On Mon, Sep 30, 2013 at 1:19 PM, Phil W <philwinfi...@gmail.com> wrote: > David, > > This is spot on and where I'd almost arrived at but what I'm ideally > looking for is to return a set of results from an input string of several > words. Unfortunately, I won't know if there are 2, 3 or 8 words and some > will not have a likeness to worry about. > > For example: 'car was red and next to a crimson motor' > > I'm really wondering if SQL alone can do this and will probably have to > try and write a function. The function will have to dynamically split the > phrase up into sections to cater for the like words and then cross join the > whole thing together. > > Thanks v much for your help so far! > > Phil > > > On Monday, 30 September 2013 16:19:37 UTC+1, ddf wrote: >> >> >> On Sunday, September 29, 2013 12:19:11 PM UTC-6, Michael Moore wrote: >>> >>> SQL> DROP TABLE syn >>> Table dropped. >>> SQL> CREATE TABLE syn >>> ( >>> val VARCHAR2 (30), >>> lik VARCHAR2 (30) >>> ) >>> Table created. >>> SQL> INSERT ALL >>> INTO syn >>> VALUES ('red', 'red') >>> INTO syn >>> VALUES ('red', 'rouge') >>> INTO syn >>> VALUES ('red', 'crimson') >>> INTO syn >>> VALUES ('car', 'car') >>> INTO syn >>> VALUES ('car', 'motor') >>> INTO syn >>> VALUES ('car', 'vehicle') >>> SELECT 1 FROM DUAL >>> 6 rows created. >>> SQL> COMMIT >>> Commit complete. >>> SQL> SELECT x.lik, y.lik >>> FROM syn x CROSS JOIN syn y >>> WHERE x.val = 'red' AND y.val = 'car' >>> >>> LIK LIK_1 >>> ------------------------------ ------------------------------ >>> red car >>> red motor >>> red vehicle >>> rouge car >>> rouge motor >>> rouge vehicle >>> crimson car >>> crimson motor >>> crimson vehicle >>> >>> 9 rows selected. >>> >>> >>> On Sun, Sep 29, 2013 at 3:51 AM, Phil W <philwi...@gmail.com> wrote: >>> >>>> Given a phase, and a table of "like" words, essentially a thesaurus, >>>> I'd like to generate all permutations of the phrase >>>> making use of the thesaurus entries. >>>> >>>> I am sure it is possible in an SQL statement as I'd like to avoid >>>> PL/SQL if possible but I am thinking now that a function/pipelined >>>> function might be my only option. Does anyone with better SQL than me know >>>> how this can be done please? >>>> >>>> create table word_syn >>>> (value varchar2(30), >>>> likeness varchar2(30)) >>>> / >>>> >>>> insert all >>>> into word_syn values ('red','rouge') >>>> into word_syn values ('red','crimson') >>>> into word_syn values ('car','motor') >>>> into word_syn values ('car','vehicle') >>>> select 1 from dual >>>> / >>>> >>>> >>>> /* Find options for "big red car" >>>> Desired output is: >>>> big rouge car >>>> big rouge motor >>>> big rouge vehicle >>>> big red motor >>>> big crimson motor... and so on. >>>> */ >>>> >>>> >>>> -- >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "Oracle PL/SQL" group. >>>> To post to this group, send email to oracle...@googlegroups.com >>>> To unsubscribe from this group, send email to >>>> Oracle-PLSQL...@googlegroups.**com >>>> For more options, visit this group at >>>> http://groups.google.com/**group/Oracle-PLSQL?hl=en<http://groups.google.com/group/Oracle-PLSQL?hl=en> >>>> >>>> --- >>>> You received this message because you are subscribed to the Google >>>> Groups "Oracle PL/SQL" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to oracle-plsql...@googlegroups.**com. >>>> For more options, visit >>>> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> >>>> . >>>> >>> >>> >> Taking this one step further: >> >> SQL> select z.likeness, alike, blike >> 2 from (select a1.likeness alike, b1.likeness blike from word_syn a1 >> cross join word_syn b1 >> 3 where b1.value = 'car' and a1.value='red') cross join word_syn z >> 4 where z.value = 'big' >> 5 / >> >> LIKENESS ALIKE BLIKE >> ------------------------------ ------------------------------ >> ------------------------------ >> big red car >> huge red car >> large red car >> gigantic red car >> grand red car >> humongous red car >> big red motor >> huge red motor >> large red motor >> gigantic red motor >> grand red motor >> >> LIKENESS ALIKE BLIKE >> ------------------------------ ------------------------------ >> ------------------------------ >> humongous red motor >> big red vehicle >> huge red vehicle >> large red vehicle >> gigantic red vehicle >> grand red vehicle >> humongous red vehicle >> big rouge car >> huge rouge car >> large rouge car >> gigantic rouge car >> >> LIKENESS ALIKE BLIKE >> ------------------------------ ------------------------------ >> ------------------------------ >> grand rouge car >> humongous rouge car >> big rouge motor >> huge rouge motor >> large rouge motor >> gigantic rouge motor >> grand rouge motor >> humongous rouge motor >> big rouge vehicle >> huge rouge vehicle >> large rouge vehicle >> >> LIKENESS ALIKE BLIKE >> ------------------------------ ------------------------------ >> ------------------------------ >> gigantic rouge vehicle >> grand rouge vehicle >> humongous rouge vehicle >> big crimson car >> huge crimson car >> large crimson car >> gigantic crimson car >> grand crimson car >> humongous crimson car >> big crimson motor >> huge crimson motor >> >> LIKENESS ALIKE BLIKE >> ------------------------------ ------------------------------ >> ------------------------------ >> large crimson motor >> gigantic crimson motor >> grand crimson motor >> humongous crimson motor >> big crimson vehicle >> huge crimson vehicle >> large crimson vehicle >> gigantic crimson vehicle >> grand crimson vehicle >> humongous crimson vehicle >> >> 54 rows selected. >> >> SQL> >> >> >> David Fitzjarrell >> > -- > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > > --- > You received this message because you are subscribed to the Google Groups > "Oracle PL/SQL" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to oracle-plsql+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/groups/opt_out. > -- -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en --- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To unsubscribe from this group and stop receiving emails from it, send an email to oracle-plsql+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.