DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Like-query as number

  1. #1
    Join Date
    Oct 2001
    Posts
    1

    Question

    We have a table in which one of the columns is described as number. The users who access this table makes queries via a GUI. The query is defined as i like-query since they often search for substrings.

    The users sees the value with preceeding zeros. Of course it should be defined as varchar in the database, but that leads to incompability with other systems in the organization and so the column is defined as number.

    Here is an example:
    select * from table_a where col1 like '000037%'

    Oracle treats '000037%' as character, ignores the index and do not get any hit in the database (we don't get the answer we expect).
    How can we force Oracle to handle '000037%' as number?


    Regards,
    jenlin

    [Edited by jenlin on 10-10-2001 at 05:40 AM]

  2. #2
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    I don't think there's any way you could get Oracle to treat '000037%' as a number. You might be able to convert it into a > or < query (which would do a range scan of the index), but you'd have to know the number of digits after the "37".

    The best option might to use a function based index on the column. The function based index could do the lpad(to_char(num_column),...) and queries which used a leading portion of the field might use the index.

    Alternatively, re-design the application. What exactly are the users looking for when they type in '0000037%' ? Is it some sort of department code, for example ? If so, you could add an extra column called department_code. When '000037%' is typed in, the query could then be converted into a more sensible one applied to the indexed department_code field.



    [Edited by nealh on 10-10-2001 at 09:25 AM]

  3. #3
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    You can remove the '%' sign from queries and convert rest of the varchar string to number
    Code:
    select to_number('000037') from dual
    /
    gives you 37.

    So you can use:
    Code:
    select * from table_a where col1 like to_number(rtrim('000037%','%'))




    [Edited by Raminder on 10-10-2001 at 06:27 AM]

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width