SQL all word permutations challenge!

[Phil W]

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.

*/

[Michael Moore]

Hi Phil,

This might help. http://plsqlnotes.blogspot.com/search/label/powermultiset. I'm not 10% sure that it applies, but I think it does.

Mike

--
--
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
 
---
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.

[Michael Moore]

Upon reviewing that blog post, I can see that it solves a very specific problem. So it is probably only marginally applicable. However, I still believe that powermultiset is the function you want. 

[Michael Moore]

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:

[ddf]

 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

[Michael Moore]

These solutions are not likely to scale very well but we can address that if it turns out to be an issue.

[Phil W]

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

[Michael Moore]

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

[Phil W]

Michael,

Sorry, I mean split into phrases with the cross join just operating at the 'like word' level as follows 

car was red and next to a crimson motor

motor was red and next to a crimson motor

vehicle  was red and next to a crimson motor

car was rouge and next to a crimson motor

car was crimson and next to a crimson motor

... and so on...

Do you think this would be possible so that the output could effectively cross join x times on the underlined (or any defined equivalent word) to provide all equivalent phrases? 

I think it is quite a challenge to do this elegantly!

Regards

Phil

[Michael Moore]

No, I don't think so unless you get into dynamic sql. You might want to abandon the cross join and look into the powermultiset function. I haven't worked it out yet, but that's where I'd be looking.

 

[ddf]

It may be that you cannot do this elegantly (or as elegantly as you might desire).  I would take Michael's advice and pursue this using powermultiset functionality.

 

 

David Fitzjarrell

[Phil Winfield]

I'm not convinced. POWERMULTISET provides me with an output of all outcomes from a set, what I want is different:

I provide A B C as my input, but B has likeness options of J, K and L and C has likeness of X and Y.

In this scenario, if I just want the possible combinations (and order is not a problem) I'm not looking at a multiset issue as far as I can see?

I'd need to swap out B for J first, then K and L. Then B & C for J and X, then J and Y and so on. The multiset option allows me to specify a set and return the results in all permutations only.

Results

A B C

A J C

A K C

A L C

A J X

A J Y

A K X

A K Y

A L X

A L Y

So, procedurally, I will code the above and return it pipelined. I was just hoping something like multiset would be available but in this situation it does not look possible.

Thank you all for the time to suggest options.

Regards

Phil

You received this message because you are subscribed to a topic in the Google Groups "Oracle PL/SQL" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/oracle-plsql/KEd0BoKeonw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to oracle-plsql...@googlegroups.com.

[Michael Moore]

Does it work if you include B as a likeness of B and C as a likeness of C? I wish I had time to think more on this. This is a great puzzle, but my darn job keeps getting in my way.

[Phil Winfield]

Yes if the order is discounted as we'd just be after the distinct set of equivalents.

Yes, the day job affects progress. I'll pick this up again at the weekend unless some clever sole beats me to it