请教各位专家:oracle某表中如何选择第一个数字前的字符插入另一字段?

Question

某一表,字段A,B都为varchar ,希望将A中每条记录中第一个数字前的内容插入字段B.
如:A:abcdefg123hij  ;  希望将 abcdefg 插入字段B. 
   A:qwert        ;    希望将 qwert   插入字段B.
请问各位专家该如何实现?有可能用sql语句就实现么?如何实现?
多谢

Answer 1

如果A字段只包含数字和字母的话可以采用
update test set B = regexp_substr(A,'[[:alpha:]]+');如果A字段包含了其他特殊字符不单单是字母和数字的话
select substr(A,1,regexp_instr(A,'[[:digit:]]+')-1) from test;A                       B
asDf123asdasdadf asDf
asEf123asdasdadf asEf
asFf123asdasdadf asFf
第二种情况
as*f123asdasdadf as*f

Answer 2

如果A字段包含了其他特殊字符不单单是字母和数字的话 
update test set B = substr(A,1,regexp_instr(A,'[[:digit:]]+')-1);

Answer 3

update tb set b = regexp_substr(a, '^\D*');

Answer 4

create table a(a1 varchar2(40));insert into a values('abcdefg123hij');
insert into a values('qwert');
select nvl(substr(a1,1,regexp_instr(a1,'[[:digit:]]')-1),a1) from a;

Answer 5

需要看使用的是什么版本的oracle数据库，oracle10g 以下版本不支持正则表达式

Answer 6

select substr('abcdefg123hij',1,regexp_instr('abcdefg123hij','[0-9]',1)-1) from dual;
但正则表达式好像只有10g以上的版本才支持

Answer 7

SELECT   SUBSTR (
            :1,
            1,
            DECODE (
               INSTR (:1, 1),
               0,
               DECODE (
                  INSTR (:1, 2),
                  0,
                  DECODE (
                     INSTR (:1, 3),
                     0,
                     DECODE (
                        INSTR (:1, 4),
                        0,
                        DECODE (
                           INSTR (:1, 5),
                           0,
                           DECODE (
                              INSTR (:1, 6),
                              0,
                              DECODE (
                                 INSTR (:1, 7),
                                 0,
                                 DECODE (
                                    INSTR (:1, 8),
                                    0,
                                    DECODE (
                                       INSTR (:1, 9),
                                       0,
                                       DECODE (INSTR (:1, 0),
                                               0, LENGTH (:1),
                                               INSTR (:1, 0)-1),
                                       INSTR (:1, 9)-1
                                    ),
                                    INSTR (:1, 8)-1
                                 ),
                                 INSTR (:1, 7)-1
                              ),
                              INSTR (:1, 6)-1
                           ),
                           INSTR (:1, 5)-1
                        ),
                        INSTR (:1, 4)-1
                     ),
                     INSTR (:1, 3)-1
                  ),
                  INSTR (:1, 2)-1
               ),
               INSTR (:1, 1)-1
            )
         )
  FROM   DUAL
任何版本皆可运行，就是，实在是太难看了！！！