mysql pgsql将字符串转成整型

pgSql

语法 to_number(text, text)

例子 select to_number(trim(both 'ibs' from classname), '999999') as cn from bbs order by cn   /*trim(both 'ibs' from classname)去除classname字段中的'ibs'字符*/

mySql

语法 str*1 / str+1 / -str  /*str为varchar的数字，此处不一定是1，只要是数字即可*/

拓展：

将字符的数字转成数字，比如'0'转成0可以直接用加法来实现
例如：将pony表中的d 进行排序，可d的定义为varchar，可以这样解决
select * from pony order by (d+0)

select max(customercode+0) from customer;

注意：customercode是字符串型varchar

hql

语法 to_number(text, text)

 

例子 select b from BBS b where b.isDeleted=false order by to_number(trim(both 'ibs' from b.className), '999999')

http://blog.youkuaiyun.com/ocean1010/article/details/7366306

http://kiko.blog.163.com/blog/static/188813820105963043523/