This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Nov 20, 2012 6:51 AM by Paul Horth RSS

String matching

916384 Newbie
Currently Being Moderated
I have the following data in DESCRIPTION column in my ITEM table

BOPP ROLL 175 MM (20 uM) (LOCAL)
BOPP ROLL 175 MM (20 uM) (PRINTED) (LOCAL)
BOPP ROLL 190 MM (20 uM) (LOCAL)
BOPP ROLL 195 MM (20 uM) (LOCAL)
BOPP ROLL 200 MM (20 uM) (LOCAL)
BOPP ROLL 200 MM (20 uM) (PRINTED) (LOCAL)
BOPP ROLL 210 MM (20 uM) (LOCAL)
BOPP ROLL 225 MM (20 uM) (LOCAL)
BOPP ROLL 225 MM (20 uM) (PRINTED) (LOCAL)
BOPP ROLL 230 MM (20 uM) (LOCAL)
BOPP ROLL 230 MM (20 uM) (PRINTED) (LOCAL)
BOPP ROLL 245 MM (20 uM) (LOCAL)
BOPP ROLL 245 MM (20 uM) (PRINTED) (LOCAL)
BOPP ROLL 265 MM (20 uM) (LOCAL)
BOPP ROLL 265 MM (20 uM) (PRINTED) (LOCAL)
BOPP ROLL 275 MM (20 uM) (LOCAL)
BOPP ROLL 280 MM (20 uM) (LOCAL)
BOPP ROLL 325MM (20 uM) (LOCAL)
BOPP ROLL 333 MM (20 uM) (LOCAL)
BOPP ROLL 333 MM (20 uM) (PRINTED) (LOCAL)
BOPP ROLL 335 MM (20 uM) (LOCAL)
BOPP ROLL 370 MM (20 uM) (LOCAL)
BOPP ROLL 50 MM (20 uM) (LOCAL)
BOPP ROLL 50 MM (20 uM) (PRINTED) (LOCAL)
BOPP SHEET 10 X 15 1/2 (20 uM) (LOCAL)
BOPP SHEET 10 X 5 1/2 (20 uM) (LOCAL)
BOPP SHEET 10 X 5 1/2 (20 uM) (LOCAL)
BOPP SHEET 10X14.5 (20 uM)
BOPP SHEET 11 1/4 X 16 3/4 (20 uM) (LOCAL)
BOPP SHEET 11 1/4 X 16 3/4 (20 uM) (LOCAL)
BOPP SHEET 11 1/4 x 18 1/2 (20 ?M) (LOCAL)

Now I want to find the data which contains 'BOPP LOCAL' at any place. I have used LIKE & SOUNDEX function for this but can't get the required result.

Regards
  • 1. Re: String matching
    jeneesh Guru
    Currently Being Moderated
    where regexp_like(description,'BOPP.*LOCAL')
  • 2. Re: String matching
    Paul Horth Expert
    Currently Being Moderated
    where description like '%BOPP%LOCAL%'
    assuming LOCAL always follows BOPP
  • 3. Re: String matching
    Manik Expert
    Currently Being Moderated
    WITH t AS
            (SELECT 'BOPP ROLL 175 MM (20 uM) (LOCAL)' str FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 175 MM (20 uM) (PRINTED) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 190 MM (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 195 MM (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 200 MM (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 200 MM (20 uM) (PRINTED) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 210 MM (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 225 MM (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 225 MM (20 uM) (PRINTED) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 230 MM (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 230 MM (20 uM) (PRINTED) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 245 MM (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 245 MM (20 uM) (PRINTED) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 265 MM (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 265 MM (20 uM) (PRINTED) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 275 MM (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 280 MM (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 325MM (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 333 MM (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 333 MM (20 uM) (PRINTED) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 335 MM (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 370 MM (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 50 MM (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP ROLL 50 MM (20 uM) (PRINTED) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP SHEET 10 X 15 1/2 (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP SHEET 10 X 5 1/2 (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP SHEET 10 X 5 1/2 (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP SHEET 10X14.5 (20 uM)' FROM DUAL
             UNION ALL
             SELECT 'BOPP SHEET 11 1/4 X 16 3/4 (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP SHEET 11 1/4 X 16 3/4 (20 uM) (LOCAL)' FROM DUAL
             UNION ALL
             SELECT 'BOPP SHEET 11 1/4 x 18 1/2 (20 ?M) (LOCAL)' FROM DUAL),
         t1 AS
            (SELECT str,
                    CASE
                       WHEN INSTR (str, 'BOPP') > 0 AND INSTR (str, 'LOCAL') > 0 THEN
                          1
                    END
                       chk
               FROM t)
    SELECT str
      FROM t1
     WHERE chk = 1;
    Cheers,
    Manik.

    Edited by: Manik on Nov 20, 2012 2:33 PM
  • 4. Re: String matching
    916384 Newbie
    Currently Being Moderated
    But If I want to search the '%BOPP%LOCAL%' then it want work for me,same case in regexp_like(description,'*LOCAL.*BOPP')

    I want to search the data in any case,no matter either it is in start or end.

    Regards
  • 5. Re: String matching
    Manik Expert
    Currently Being Moderated
    Did you check my post.. just use INSTR function to verify whether string exists or not for both the words... It should work irrespective of postion of the words in the search string.

    Cheers,
    Manik.
  • 6. Re: String matching
    Paul Horth Expert
    Currently Being Moderated
    fahedakhter wrote:
    But If I want to search the '%BOPP%LOCAL%' then it want work for me,same case in regexp_like(description,'*LOCAL.*BOPP')

    I want to search the data in any case,no matter either it is in start or end.

    Regards
    Sorry, that's not clear to me. are you saying you want it to pick up

    LOCAL sdfgafdg BOPP

    as well?

    In which case just use and OR condition with the words in the other order.
    where description like '%BOPP%LOCAL%'
    or description like '%LOCAL%BOPP%'
    Edited by: Paul Horth on 20-Nov-2012 01:29
  • 7. Re: String matching
    jeneesh Guru
    Currently Being Moderated
    fahedakhter wrote:
    But If I want to search the '%BOPP%LOCAL%' then it want work for me,same case in regexp_like(description,'*LOCAL.*BOPP')

    I want to search the data in any case,no matter either it is in start or end.

    Regards
    What Paul postd will work whereever that two string comes in order (Start or end or middle..) and it is better than regexp...
    If you want to ignore the order of those two strings, you can do like
    where description like '%BOPP%LOCAL%'
    or description like '%LOCAL%BOPP%'
  • 8. Re: String matching
    971895 Journeyer
    Currently Being Moderated
    use UPPER OF THE COLUMN
  • 9. Re: String matching
    916384 Newbie
    Currently Being Moderated
    The complete scenario is, there are thousands of items present in my ITEM table, Now I want to check before open new item that the item present on that master table or not. For this checking i want to check it for multiple condition, for example 'BOPP LOCAL','LOCAL BOPP','BOPP','12MM BOPP LOCAL' etc.

    Regards
  • 10. Re: String matching
    Paul Horth Expert
    Currently Being Moderated
    fahedakhter wrote:
    The complete scenario is, there are thousands of items present in my ITEM table, Now I want to check before open new item that the item present on that master table or not. For this checking i want to check it for multiple condition, for example 'BOPP LOCAL','LOCAL BOPP','BOPP','12MM BOPP LOCAL' etc.

    Regards
    are you now saying you want to find 'BOPP' on its own without 'LOCAL'?

    Please make your mind up.
  • 11. Re: String matching
    916384 Newbie
    Currently Being Moderated
    Actually the item name may be duplicate at any case, either it is 'BOPP LOCAL 12" ','LOCAL BOPP 12" ','12" BOPP LOCAL','12" LOCAL BOPP','LOCAL 12" BOPP'.

    This is the possible combination, may be it gets increase.

    I want to find there possible combination .

    Regards
  • 12. Re: String matching
    Paul Horth Expert
    Currently Being Moderated
    fahedakhter wrote:
    Actually the item name may be duplicate at any case, either it is 'BOPP LOCAL 12" ','LOCAL BOPP 12" ','12" BOPP LOCAL','12" LOCAL BOPP','LOCAL 12" BOPP'.

    This is the possible combination, may be it gets increase.

    I want to find there possible combination .

    Regards
    And what was wrong with my previous answer?
    {code}
    where description like '%BOPP%LOCAL%'
    or description like '%LOCAL%BOPP%'
    {code}

    that will find everything in your example.
  • 13. Re: String matching
    916384 Newbie
    Currently Being Moderated
    Here is the query.

    SELECT DESCRIPTION
    FROM ITEM
    WHERE DESCRIPTION LIKE &INPUT

    I want to match the user input from ITEM table that what possible combination are exist in table.

    Regards
  • 14. Re: String matching
    Paul Horth Expert
    Currently Being Moderated
    fahedakhter wrote:
    Here is the query.

    SELECT DESCRIPTION
    FROM ITEM
    WHERE DESCRIPTION LIKE &INPUT

    I want to match the user input from ITEM table that what possible combination are exist in table.

    Regards
    You seem to have changed your requirements again :-)

    Please don't waste people's time like that. In your original post, please state clearly EXACTLY what you want.

    Which now seems to be, I want to enter an arbitrary string of words and looks for any combination of those words in DESCRIPTION.

    so for example if &INPUT was 'PLEASE EXPLAIN CLEARLY' then you would want to pick up descriptions like
    'PLEASE COULD YOU EXPLAIN CLEARLY'
    'EXPLAIN CLEARLY PLEASE'
    'CLEARLY I AM NOT EXPLAINING PLEASE'
    'EXPLAINPLEASECLEARLY'
    'AARGH! PLEASE! CLEARLY! BONGEXPLAINKRUMP'

    Is that right?

    If not, make the effort to give correct sample data, example &INPUT strings, and the output you expect in each case.

    The more effort you put into that, the better we can help.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points