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