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