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?
AppFabric Installation Problem
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
This would still include '1AA2' in the result set..
SELECT telnumber FROM numbers n WHERE n.telnumber BETWEEN '1234' AND '9999' AND LENGTH(n.telnumber) = 4
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
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'..
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]'
Further:. However, as Jonathan Leffler pointed out previously, it sounds like your dataset contains values which aren't necessarily cast-able to integer.
SELECT telnumber FROM numbers n WHERE n.telnumber::integer BETWEEN 1234 AND 9999;
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:.
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.
SELECT telnumber FROM numbers n WHERE n.telnumber not matches "*[0-9]*" AND n.telnumber::integer 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..
SELECT telnumber FROM numbers WHERE Abs(n.telnumber) BETWEEN 1234 AND 9999
Select * From table1 where cast(left(field1,7) as numeric) BETWEEN 2128042 and 2128045