Convert varchar to numeric in Informix


Convert varchar to numeric in Informix



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




Can I rely on MSXML4 being present?

1:



AppFabric Installation Problem
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..
Domain Name Windows Vista Does Not Exist In The Database
The second query lists the extra values because '123' comes between '10' and '20' when compared as strings..
Packaging and Distribution of Java Program
If you want to limit it to 4-digit numbers, then you could use:.
A solution to embed affiliate data (id/logo) into an installer
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..
Installation of Expression Studio 3 fails
Full regular expression support (such as PCRE) is not present as standard in IDS - sadly.


Upgrade installation results in duplicate entry for installed software (FindRelatedProducts not executed)
However, the non-standard MATCHES operator would allow you to do it:.
Uninstall an MSI with msiexec fails with new version of MSI
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'..


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.


3:


I do not use Informix, but this works for me in SQL Server:.
SELECT telnumber FROM numbers WHERE Abs(n.telnumber) BETWEEN 1234 AND 9999 
However, you will probably run into problems because telephone numbers really are strings and are not INTs, and ultimately any attempt to treat them as INTs will fail..


4:


For me, this worked:.
Select * From table1 where cast(left(field1,7) as numeric) BETWEEN 2128042 and 2128045 



80 out of 100 based on 70 user ratings 620 reviews