Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005

    Unanswered: text and numbers

    Is it possible to consider numbers stored in a text field as number during a query or a sort?
    In a text field the user should write text or number, it depends on another field, but during a query with a comparison as >= the number is treated like text, and the result is not what is espected.
    The second field involved is used into the query, so there is no mix between text and numbers.

    Thanks for any help

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    In Oracle, you would use the TO_NUMBER function, for example:

    SELECT * FROM your_table
    WHERE TO_NUMBER(this_column) >= some_value;

    This statement will raise an error if "this_column" contains data different than numbers (for example, values like 'abc', 'a123', '#$%' etc.). It means that you'd have to, additionally, check for column value before using this function.

    Furthermore, if you have index created on column "this_column", it would then NOT be used (but there's possibility for you to create an index based on this function, which would then be used in your queries).

  3. #3
    Join Date
    Feb 2005
    There should be equivalent functions to TO_NUMBER in any database system you are using e.g. CONVERT/CAST in SQL Server. Sometimes you have to look hard to find them though. If you have a fixed length field or the data is added with blanks around it you might need to TRIM them off first.

Posting Permissions

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