sqlserver中的一段sql 转oracle

wudi_1982 2008-02-15 09:55:58
数据本身是由多个二进制数据拼接出来的,存入数据库时是按照varchar2(66)存储的。。现在要解析出来,在sqlserver中是这么写的
select cast(substring(cast(ageing_a as varbinary),1,5) as bigint) as age1,
cast(substring(cast(ageing_a as varbinary),6,5) as bigint) as age2,
cast(substring(cast(ageing_a as varbinary),11,5) as bigint) as age3,
cast(substring(cast(ageing_a as varbinary),16,5) as bigint) as age4,
cast(substring(cast(ageing_a as varbinary),21,5) as bigint) as age5,
cast(substring(cast(ageing_a as varbinary),26,5) as bigint) as age6
from tablename

不太熟悉oracle,忘各位帮忙,看看在oracle中使用pl/sql应该怎么写。谢谢。
...全文
115 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
dwashy123 2008-02-18
  • 打赏
  • 举报
回复
up
常飞梦 2008-02-18
  • 打赏
  • 举报
回复
UP
dawugui 2008-02-17
  • 打赏
  • 举报
回复
你给出原始数据和结果吧.这样好做,不然猜你的意思太累.

不过写法就是上面的,具体的转换你查查函数.
wudi_1982 2008-02-16
  • 打赏
  • 举报
回复
HEX_TO_RAW 这个好像是十六进制转二进制的啊。。

数据实际是二进制的,但被存储为字符串
dawugui 2008-02-15
  • 打赏
  • 举报
回复
select TO_NUMBER(substr(HEX_TO_RAW(ageing_a),1,5)) age1,
TO_NUMBER(substr(HEX_TO_RAW(ageing_a),6,5)) age2,
TO_NUMBER(substr(HEX_TO_RAW(ageing_a),11,5)) age3,
TO_NUMBER(substr(HEX_TO_RAW(ageing_a),16,5)) age4,
TO_NUMBER(substr(HEX_TO_RAW(ageing_a),21,5)) age5,
TO_NUMBER(substr(HEX_TO_RAW(ageing_a),26,5)) age6
from tablename

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧