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'.

Answer 2

Simple Answer: Informix has built in casting, which you can use like this:

SELECT telnumber
FROM   numbers n
WHERE  n.telnumber::integer BETWEEN 1234 AND 9999;

Further:

However, as Jonathan Leffler pointed out previously, it sounds like your dataset contains values which aren't necessarily cast-able to integer. If that's so, then perhaps this isn't the ideal schema for your purposes? If you absolutely need to have non-numeric characters in here (eg I suspect you'll end up with one of '-', '(' or ')'), you could try excluding rows that match non-numeric chars:

SELECT telnumber
FROM   numbers n
WHERE  n.telnumber not matches "*[0-9]*"
AND    n.telnumber::integer BETWEEN 1234 AND 9999;

Alternatively, you could maybe try using a stored procedure, utilising an ON EXCEPTION block? (Character to numeric conversion error is errno 1213).

Further reading on using 'ON EXCEPTION': http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls946.htm

Further reading on defining and using stored procedures: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls906.htm