oracle字符串10进制相加,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进制数可以表示的话

则有

* from tt;

ID A

---------- ----------

1 00100000

1 000e0000

1 000000b0

2 f0000000

2 0000c000

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进制能显示的话，更简化一些

* from tt;

ID A

---------- ----------

1 00100000

1 000e0000

1 000000b0

2 f0000000

2 0000c000

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进制能显示的话，更简化一些

* from tt;

ID A

---------- ----------

1 00100000

1 000e0000

1 000000b0

2 f0000000

2 0000c000

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

/