oracle中有没有针对字符串做位运算的方法 ？

4pal提出如下问题：

数据库中有如下数据：
字段1 字段2
1 0a000000
1 c0000000
1 00000b00
1 000a0000
2 0000000d
2 00000a00
3 0c000000
3 0000b000
3 000000g0
......

字段2是个N位字符串，有规律的，要么全部是0，要么只有一位是英文字母

我想根据字段1做group，将字段2合并起来（相当于位操作），结果应该是：
1 ca0a0b00
2 00000a0d
3 0c00b0g0
.....

可有现成的函数能够处理呢？

jlandzpa的一个笨办法 ：
SQL> select id,
2 replace(chr(sum(decode(ascii(substr(a,1,1)),48,0,ascii(substr(a,1,1)))))||
3 chr(sum(decode(ascii(substr(a,2,1)),48,0,ascii(substr(a,2,1)))))||
4 chr(sum(decode(ascii(substr(a,3,1)),48,0,ascii(substr(a,3,1)))))||
5 chr(sum(decode(ascii(substr(a,4,1)),48,0,ascii(substr(a,4,1)))))||
6 chr(sum(decode(ascii(substr(a,5,1)),48,0,ascii(substr(a,5,1)))))||
7 chr(sum(decode(ascii(substr(a,6,1)),48,0,ascii(substr(a,6,1)))))||
8 chr(sum(decode(ascii(substr(a,7,1)),48,0,ascii(substr(a,7,1)))))||
9 chr(sum(decode(ascii(substr(a,8,1)),48,0,ascii(substr(a,8,1))))),chr(0),'0')
10 from tt group by id;

ID REPLACE(CHR(SUM(
---------- ----------------
1 ca0a0b00
2 00000a0d
3 0c00b0g0

 
biti_rainy的解决方法：

 如果你的字符能控制在 0--9 , a--f 这 16个字符 ，确保16进制数可以表示的话

则有

select">alibaba@OCN>select * from tt;

ID A
---------- ----------
1 00100000
1 000e0000
1 000000b0
2 f0000000
2 0000c000

select">alibaba@OCN>select id, lpad(trim(to_char(sum(to_number(a,'xxxxxxxxxxxx')),'FMxxxxxxxx')),8,'0')
from tt
group by id;
2 3
ID LPAD(TRI
---------- --------
1 001e00b0
2 f000c000

 16进制能显示的话，更简化一些
select">alibaba@OCN>select * from tt;

ID A
---------- ----------
1 00100000
1 000e0000
1 000000b0
2 f0000000
2 0000c000

select">alibaba@OCN>select id, to_char(sum(to_number(a,'xxxxxxxxxxxx')),'0xxxxxxx')
2 from tt
group by id; 3

ID TO_CHAR(S
---------- ---------
1 001e00b0
2 f000c000

 16进制能显示的话，更简化一些
select">alibaba@OCN>select * from tt;

ID A
---------- ----------
1 00100000
1 000e0000
1 000000b0
2 f0000000
2 0000c000

select">alibaba@OCN>select id, to_char(sum(to_number(a,'xxxxxxxxxxxx')),'0xxxxxxx')
2 from tt
group by id; 3

ID TO_CHAR(S
---------- ---------
1 001e00b0
2 f000c000

nyfor的解决方法：

create table tt(id number,a varchar2(10));

insert into TT (ID, A) values (1, '0a000000');
insert into TT (ID, A) values (1, 'c0000000');
insert into TT (ID, A) values (1, '00000b00');
insert into TT (ID, A) values (1, '000a0000');
insert into TT (ID, A) values (2, '0000000d');
insert into TT (ID, A) values (2, '00000a00');
insert into TT (ID, A) values (3, '0c000000');
insert into TT (ID, A) values (3, '0000b000');
insert into TT (ID, A) values (3, '000000g0');
commit;

create type tab_str is table of varchar2(100);

create or replace function f_merge(p tab_str) return varchar2
is
  lr raw(100);
begin
  if p.count <= 0 then
    return null;
  end if;
  lr := utl_raw.copies('FF',length(p(1)));
  for i in 1..p.count loop
    lr := utl_raw.bit_and(lr,utl_raw.cast_to_raw(replace(p(i),'0',chr(255))));
  end loop;
  return replace(utl_raw.cast_to_varchar2(lr),chr(255),'0');
end;
/

select id,f_merge(cast(multiset(select a from tt where id = x.id) as tab_str)) a
  from (select id from tt group by id) x;

      ID A
-------- ----------
       1 ca0a0b00
       2 00000a0d
       3 0c00b0g0

drop function f_merge;
drop type tab_str;
drop table tt;

假定你的字符串长度不大，较小，且同一位置不会有重复的字母，也可以使用下面这个

代码:--------------------------------------------------------------------------------
这里假设是8位字符串：
select id,
       utl_raw.cast_to_varchar2(hextoraw(replace(to_char(sum(to_number(utl_raw.cast_to_raw(replace(a,
                                                                                                   '0',
                                                                                                   chr(0))),
                                                                       'xxxxxxxxxxxxxxxx')),
                                                         'fm000000000000000x'),
                                                 '00',
                                                 '30'))) a
  from tt
 group by id
/

http://www.itpub.net/showthread.php?threadid=256540&pagenumber=

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60461/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/936/viewspace-60461/