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
On Monday, 30 September 2013 21:37:27 UTC+1, Michael Moore wrote:
>
> 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 <[email protected] <javascript:>
> > 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 <[email protected]> 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 [email protected]
>>>>> 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 [email protected]<javascript:>
>> To unsubscribe from this group, send email to
>> [email protected] <javascript:>
>> 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 [email protected] <javascript:>.
>> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
