Convert varchar to numeric in Informix

Question

I have problem while converting varchar type to Int type in Informix. Actually I don't know if the value is really varchar or not which I want to convert to INT. It's a sandbox system.

As Example: I am trying to run this kind of

Select telnumber from numbers n
 where Cast(n.telnumber AS INT) between 1234 and 9999

I got this error: "Character to numeric conversion error"

If I run this query like this:

Select telnumber from numbers n where n.telnumber between '1234' and '9999'

it brings a resultset but not in the range that I defined.

130987
130710
130723

How can I convert telnumber to a numeric value and use it in "between" 1234 and 9999 range

Answer 1

The conversion error suggests that some of the values in the telnumber column are not formatted as a valid integer - and therefore trigger the error you see when conversion is attempted.

The second query lists the extra values because '123' comes between '10' and '20' when compared as strings.

If you want to limit it to 4-digit numbers, then you could use:

SELECT telnumber
  FROM numbers n
 WHERE n.telnumber BETWEEN '1234' AND '9999'
   AND LENGTH(n.telnumber) = 4

This would still include '1AA2' in the result set.

Full regular expression support (such as PCRE) is not present as standard in IDS - sadly. However, the non-standard MATCHES operator would allow you to do it:

SELECT telnumber
  FROM numbers n
 WHERE n.telnumber BETWEEN '1234' AND '9999'
   AND LENGTH(n.telnumber) = 4
   AND n.telnumber MATCHES '[0-9][0-9][0-9][0-9]'

That is a simple regular expression - but the '*' is a shell globbing style 'any sequence of zero or more characters' rather than the Kleene Star 'zero or more repetitions of the previous character'.