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"

如果我这样说:

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

它带来了结果,但并非我界定的范围。

130987
130710
130723

我怎么能将电话数转换成数字数值,并在1234和99999米之间使用。

Answer 1

转换错误表明,电话编号栏中的某些数值不作为有效的分类,因此触发了你在试图转换时看到的错误。

第二点列出了额外价值,因为123项与指示相比在10到20之间。

如果你想将其限制在4位数上,那么你可以使用:

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

这仍然包括设定的结果中的1A2。

完全的定期表达支持(如PCRE)在《国际数据集》中并不是标准,令人痛心。然而,非标准MATCHES的运营商将允许你这样做:

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]

这是一种简单的定期表述,但* 是一种徒劳的游说方式,其性质为零或更多,而不是像以前特性那样的“Klee Star零”或更重复。

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;

此外:

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;

或者,你可以尝试使用一个储存的程序,利用一个全国EXCEPTION区? (Character改为数字换算错误为1213卢比)。

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls946.htm rel=“nofollow noreferer” http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sql.doc/sqlsqls946.htm

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls906.htm

Answer 3

我没有使用Informix,但为SQ服务器进行的这项工作:

SELECT telnumber FROM numbers WHERE Abs(n.telnumber) BETWEEN 1234 AND 9999

然而,你可能会遇到问题,因为电话号码确实是插手的,不是信使,最终将试图把电话号码当作信使。

Answer 4

对我来说,这项工作是:

Select *
From table1
where cast(left(field1,7) as numeric) BETWEEN 2128042 and 2128045

友情链接