1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Searching Oracle Table column with regexp based on user input

Discussion in 'SQL PL/SQL' started by deep.singh, May 1, 2010.

  1. deep.singh

    deep.singh Guest

    I have table in Oracle with one column PRODUCT. Column PRODUCT have following values -
    Account Management
    Active Directory
    Adobe Acrobat Reader
    NT Account
    Application Security
    Beehive Conference
    WebSite Account
    Beehive Workspace
    CTI Data Warehouse
    Hardware Keyboard
    HP Laptop

    I am designing application where I need to search for PRODUCT based upon user's input. Lets say user wants search on 'Laptop Account Broken'.
    I want to search for all products which contains any of words in user's input. So based upon user's input I want output like below.

    ---------------
    Expected Output
    ---------------
    Account Management
    NT Account
    WebSite Account
    HP Laptop

    I need your help to write me SQL for my desired output. please help -
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Re: Searching PRODUCT column based upon user's input

    Hey deep

    It's not a good idea asking others to do your work. It's considered impolite. Anyways, I dare say your problem is interesting, but still i would not want to write a query until i see some effort on your part.

    Regards
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Searching PRODUCT column based upon user's input

    Normally I'd have you post what you've written but this is a bit more complex than it appears:

    Code (SQL):
     
    SQL> CREATE TABLE prod_desc(product     varchar2(60));
    TABLE created.
    SQL>
    SQL> INSERT ALL
      2  INTO prod_desc VALUES ('Account Management')
      3  INTO prod_desc VALUES ('Active Directory')
      4  INTO prod_desc VALUES ('Adobe Acrobat Reader')
      5  INTO prod_desc VALUES ('NT Account')
      6  INTO prod_desc VALUES ('Application Security')
      7  INTO prod_desc VALUES ('Beehive Conference')
      8  INTO prod_desc VALUES ('WebSite Account')
      9  INTO prod_desc VALUES ('Beehive Workspace')
     10  INTO prod_desc VALUES ('CTI Data Warehouse')
     11  INTO prod_desc VALUES ('Hardware Keyboard')
     12  INTO prod_desc VALUES ('HP Laptop')
     13  SELECT * FROM dual;
    11 ROWS created.
    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> SELECT * FROM prod_desc;
    PRODUCT
    ------------------------------------------------------------
    Account Management
    Active Directory
    Adobe Acrobat Reader
    NT Account
    Application Security
    Beehive Conference
    WebSite Account
    Beehive Workspace
    CTI DATA Warehouse
    Hardware Keyboard
    HP Laptop
    11 ROWS selected.
    SQL>
    SQL> variable list varchar2(40)
    SQL>
    SQL> BEGIN
      2          :list := 'Laptop Account Broken';
      3  END;
      4  /
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> SELECT product
      2  FROM prod_desc
      3  WHERE regexp_like(product, '*'||substr(:list, 1, instr(:list,' ', 1, 1)-1)||'*')
      4  OR regexp_like(product, '*'||substr(:list, instr(:list,' ', 1, 1)+1, (instr(:list,' ', -1, 1)- instr(:list,' ', 1, 1)+1) -1)||'*')
      5  OR regexp_like(product, '*'||substr(:list, instr(:list,' ', -1, 1)+1)||'*');
    PRODUCT
    ------------------------------------------------------------
    Account Management
    NT Account
    WebSite Account
    HP Laptop
    SQL>

     
    This is only an example to get you started; I leave it to you to figure out how to do this for any string passed to the query.