This discussion is archived
2 Replies Latest reply: Jun 19, 2012 12:15 PM by 924529 RSS

Number Search using Oracle Text

924529 Newbie
Currently Being Moderated
Hi,

I'm new to Oracle Text. I want to implement search for the unique ids. Like google search when the user start typing 123 it need to brings anything starting with 123 and has show like entries how google will shows. When I add number 4 to like 1234 then it has bring numbers starting with 1234. Any body suggest me what is the best approach to implement this. Do we have any examples for this kind requirement. If any please share. That will be a big help for me.

In advance thanks a lot for the help.

Thanks & Regards,
Ramana.
  • 1. Re: Number Search using Oracle Text
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    Oracle Text is intended primarily for character data, but you can treat the numbers as characters. I have provided an example below.
    SCOTT@orcl_11gR2> CREATE TABLE test_tab (id  NUMBER, dummy VARCHAR2(1))
      2  /
    
    Table created.
    
    SCOTT@orcl_11gR2> INSERT INTO test_tab (id)
      2  SELECT object_id FROM all_objects
      3  /
    
    74986 rows created.
    
    SCOTT@orcl_11gR2> BEGIN
      2    CTX_DDL.CREATE_PREFERENCE ('test_mds', 'MULTI_COLUMN_DATASTORE');
      3    CTX_DDL.SET_ATTRIBUTE ('test_mds', 'COLUMNS', 'TO_CHAR(id) id');
      4  END;
      5  /
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> CREATE INDEX id_idx ON test_tab (dummy)
      2  INDEXTYPE IS CTXSYS.CONTEXT
      3  PARAMETERS ('DATASTORE  test_mds')
      4  /
    
    Index created.
    
    SCOTT@orcl_11gR2> VARIABLE search_string VARCHAR2(100)
    SCOTT@orcl_11gR2> EXEC :search_string := '123'
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> SELECT DISTINCT id
      2  FROM   test_tab
      3  WHERE  CONTAINS (dummy, :search_string || '%') > 0
      4  /
    
            ID
    ----------
           123
         12316
         12323
         12329
         12348
         12350
         12354
         12366
         12372
         12373
         12375
         12376
         12392
         12396
          1238
         12303
         12306
         12313
         12330
         12332
         12344
         12361
         12365
         12369
         12371
         12395
        123750
         12308
         12320
         12326
         12337
         12340
         12346
         12349
         12370
         12386
         12387
         12397
          1233
          1234
          1236
          1237
         12300
         12301
         12302
         12305
         12309
         12311
         12319
         12338
         12345
         12355
         12359
         12362
         12377
         12388
         12390
         12391
         12398
          1239
         12304
         12307
         12360
         12318
         12325
         12335
         12339
         12353
         12357
         12358
         12394
         12312
         12317
         12328
         12327
         12331
         12333
         12351
         12352
         12356
         12363
         12364
         12367
         12368
         12393
          1235
         12310
         12314
         12324
         12334
         12336
         12341
         12347
         12374
         12389
         12399
    
    96 rows selected.
    
    SCOTT@orcl_11gR2> EXEC :search_string := '1234'
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> /
    
            ID
    ----------
         12348
         12344
         12340
         12346
         12349
          1234
         12345
         12341
         12347
    
    9 rows selected.
  • 2. Re: Number Search using Oracle Text
    924529 Newbie
    Currently Being Moderated
    Hi Barbara,

    Thanks a lot for quick reply. Its really helpful.

    Regards,
    Ramana.

Legend

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