Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Unanswered: Char to Int conversion

    Is there any function in informix sql that converts char to int for use with relational operators (>, <, <= etc.)
    I am using a char type column that contains numeric data, and need to compare data in this column with specific numbers. The query does not return correct results when I use the following where clause:
    ...where card_no[1,9] < "40110018"; In this case the query returns rows that contain 40110019 and greater numbers, even 9 and 10 digit numbers.

    Where as if I use ...where card_no[1,9] < 40110018; --wihtout quotes
    the query returns a character to numeric conversion error.

    What is the correct way (maybe some function...) to compare char (actually integer) values with another number.

    Thanks in advance.

  2. #2
    Join Date
    Aug 2003

    If you work with characters, the correct form is columnname[x,y], but if you work with numbers, then does not.
    I think that you can create a stored procedure and convert value with a LET.


  3. #3
    Join Date
    Apr 2003
    Phoenix, AZ
    You need to CAST the character field (or a piece of it) as a true number (like integer) and then you can perform your compares.

    For example:

    select first 20 ssn
    from client_ssn
    where cast (ssn as integer) between 300000000 and 400000000

    select first 20 ssn
    from client_ssn
    where cast (ssn[4,5] as integer) = 67
    Fred Prose

Posting Permissions

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