需要将Oralce中的Blob类型转成字符串。现在数据库中有一个字段是blob类型,里面存放的文本。
我要用 字符串 与这个字段做where查询。如 select * from tab where blob_= '中文';
blob_字段为Blob类型, 在mysql中可以unhex(hex(blob_))来得到字符串,那在Oracle中语句怎么写? 跪求。
tips: No oracle customize function.
我要用 字符串 与这个字段做where查询。如 select * from tab where blob_= '中文';
blob_字段为Blob类型, 在mysql中可以unhex(hex(blob_))来得到字符串,那在Oracle中语句怎么写? 跪求。
tips: No oracle customize function.
直接TO_CHAR(BLOB_) = '中文'
to_char 不行, 报错。 数据量很小
SQL> select UTL_RAW.CAST_TO_VARCHAR2(a) from t_blob t where UTL_RAW.CAST_TO_VARCHAR2(a)='12321';
UTL_RAW.CAST_TO_VARCHAR2(A)
--------------------------------------------------------------------------------
12321
SQL>
SQL> select UTL_RAW.CAST_TO_VARCHAR2(a) from t_blob t ;
UTL_RAW.CAST_TO_VARCHAR2(A)
--------------------------------------------------------------------------------
中国
使用UTL_RAW.CONVERT来转换一下
例如
UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.CONVERT(BLOB_,'AMERICAN_AMERICA.US7ASCII','AMERICAN_AMERICA.ZHS16GBK'))
SIMPLIFIED CHINESE_CHINA.AL32UTF8
存储文本的话,可以使用clob来代替blob
好把。 我现在要转。。 直接告诉我sql语句把。
select count(*) as col_0_0_ from score_news_online scorenewso0_ where scorenewso0_.onlineFlag=1 and trim(unhex(hex(scorenewso0_.oneTitle)))=trim(unhex('D5AEC8A8')) and scorenewso0_.newsType='news_sc_newstype_yxdt' and (scorenewso0_.assTitle between '10000000' and '50000000')--oracle
select count(*) as col_0_0_ from bp_winner_ticketphoto scorenewso0_ where convert(UTL_RAW.CAST_TO_VARCHAR2(scorenewso0_.photodata), 'utf8', 'zhs16gbk')='债权'
and scorenewso0_.newsType='news_hub_newstype_yxdt' and (scorenewso0_.assTitle between '10000000' and '50000000')