不知道是不是这个意思
SQL> select * from temp;CA CB
-------------------------------------------------- --
123""456""789""0 2
123""456 1SQL> select cb from (select translate(ca,'"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','"') ca,cb
2 from temp)
3 where length(ca)=(
4 select max(length(translate(ca,'"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','"')))
5 from temp);CB
--
2SQL>
SQL> select * from temp;CA CB
-------------------------------------------------- --
123""456""789""0 2
123""456 1SQL> select cb from (select translate(ca,'"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','"') ca,cb
2 from temp)
3 where length(ca)=(
4 select max(length(translate(ca,'"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','"')))
5 from temp);CB
--
2SQL>
Syntax TRANSLATE(char, from, to)
Purpose Returns char with all occurrences of each character in from replaced by
its corresponding character in to. Characters in char that are not in
from are not replaced. The argument from can contain more characters
than to. In this case, the extra characters at the end of from have no corresponding
characters in to. If these extra characters appear in char,
they are removed from the return value. You cannot use an empty
string for to to remove all characters in from from the return value.
Oracle interprets the empty string as null, and if this function has a
null argument, it returns null.
Example 1 The following statement translates a license number. All letters
’ABC...Z’ are translated to ’X’ and all digits ’012 . . . 9’ are translated to
’9’:
SELECT TRANSLATE(’2KRW229’,
’0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’,
’9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX’) "License"
FROM DUAL;
License
--------
9XXX999
Example 2 The following statement returns a license number with the characters
removed and the digits remaining:
SELECT TRANSLATE(’2KRW229’,
’0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’,
’0123456789’)
"Translate example"
FROM DUAL;
Translate example
-----------------
2229
能否举例说明呢?
translate(p1,'abc','xyz')
将p1字符串中所有a变成x,b变成y,c变成z,是按位替换的。
Purpose
Returns char with all occurrences of each character in from replaced by its corresponding character in to. Characters in char that are not in from are not replaced. The argument from can contain more characters than to. In this case, the extra characters at the end of from have no corresponding characters in to. If these extra characters appear in char, they are removed from the return value. You cannot use an empty string for to to remove all characters in from from the return value. Oracle interprets the empty string as null, and if this function has a null argument, it returns null.
Example 1
The following statement translates a license number. All letters 'ABC...Z' are translated to 'X' and all digits '012 . . . 9' are translated to '9':
SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
FROM DUAL;
License
--------
9XXX999
Example 2
The following statement returns a license number with the characters removed and the digits remaining:
SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')
"Translate example"
FROM DUAL;
Translate example
-----------------
2229
-------------------------------------------- -------------
abc bca zhonghua 000000001
abc bca aaa dsd 000000002
abc 123 vcx 000000003
abc fdc aaa ccc 000000004
abc bca dsd 000000005
如果字符串数组为{"abc"} 返回任一记录都可以
如果字符串数组为{"123"} 返回的只有000000003
如果字符串数组为{"123","abc"}返回的只有000000003
如果字符串数组为{"aaa","abc"}返回的000000002或着00000004中一条
如果字符串数组为{"aaa","bca","abc"}就只有000000002了
如果字符串数组为{"dsd","bca","abc"}就只有000000005了
大概就是这意思
用select cb from temp where instr(ca,'aaa') > 0 and instr(ca,'abc') > 0
因为oracle中没有你提供的那种形式的数组。应该怎么处理这条sql语句你需要考虑。
另外由于数组中的个数不定,必须考虑动态sql。
create or replace type mytabletype as table of number;
/create or replace function strtab(p_str in varchar2)
return mytabletype
as
lstr varchar2(1000) default p_str||',';
ln number;
ldata mytabletype:=mytabletype();
begin
loop
ln:=instr(lstr,',');
exit when (nvl(ln,0)=0);
ldata.extend;
ldata(ldata.count):=ltrim(rtrim(substr(lstr,1,ln-1)));
lstr:=substr(lstr,ln+1);
end loop;
return ldata;
end;
/SQL> select * from table(cast(strtab('11,12,13') as mytabletype));COLUMN_VALUE
------------
11
12
13SQL> create table bb(id varchar2(2),name varchar2(10));Table createdSQL> insert into bb values('11','张三');1 row insertedSQL> insert into bb values('12','李四');1 row insertedSQL> insert into bb values('13','王五');1 row insertedSQL> select * from bb where id in (select * from table(cast(strtab('11,12,13') as mytabletype)));ID NAME
-- ----------
11 张三
12 李四
13 王五