oracle to_number() 的兼容性查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17780412/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
compatibility Query for to_number()
提问by karthik.A.M
In this query using to_number()in oracle. How to write compatibility query for oracle and mysql databases.
在这个查询中使用to_number()了oracle。如何编写oracle 和mysql 数据库的兼容性查询。
SELECT col1 FROM table WHERE condition ORDER BY TO_NUMBER(col2);
Here col2is varchar2datatype. Suppose i was used ORDER BYcommand in this query must use converting function i.e to_number(col2),this function not available in mysql.so please give correct solution for above problem
这col2是varchar2数据类型。假设我ORDER BY在此查询中使用的命令必须使用转换功能，即to_number(col2)该功能在 mysql.so 中不可用，因此请针对上述问题给出正确的解决方案
回答by Akhil
Create a custom function in your mysql db with name to_numberwhich takes same parameter and returns integer .
You can then use castfunction inside your custom function
在您的 mysql 数据库中创建一个自定义函数，其名称to_number采用相同的参数并返回 integer 。
然后您可以cast在自定义函数中使用函数
DELIMITER $$DROP FUNCTION IF EXISTS to_number$$CREATE FUNCTION to_number (number VARCHAR(10)) RETURNS INT (11)BEGINRETURN (CAST(number AS SIGNED));END$$DELIMITER ;
This will create a custom/userdefined function with to_number as name
Then you can use your query both in oracle and mysql
DELIMITER $$DROP FUNCTION IF EXISTS to_number$$CREATE FUNCTION to_number (number VARCHAR(10)) RETURNS INT (11)BEGINRETURN (CAST(number AS SIGNED));END$$DELIMITER ;
这将创建一个以 to_number 作为名称的自定义/用户定义函数
然后您可以在 oracle 和 mysql 中使用您的查询
回答by Suresh Kamrushi
You can do it with CAST
你可以用 CAST 做到
select id,num type, details,CAST(num AS SIGNED) as T
from demo order by T
SQL Fiddle link
SQL小提琴链接
For more info : http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html
欲了解更多信息：http: //dev.mysql.com/doc/refman/5.0/en/cast-functions.html
