oracle 正则表达式查找后跟5位数字的字母

hc8w905p 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(89)

我正在使用Oracle SQL，需要一种方法来定位和输出一个字母后跟5位数字。这可能根本不存在，或者可能被其他字符包围。以下是一些样本数据：

select '**' col from dual
union all
select '+D91882' from dual
union all
select '3DD12523' from dual
union all
select 'Testing' from dual
union all
select 'A12345' from dual
union all
select '3DR4096' from dual
union all
select 'G92648RE' from dual
union all
select 'D123456' from dual

按顺序列出的输出应为：
null
D91882
D12523
null
A12345
G92648
D12345

oracle

来源：https://stackoverflow.com/questions/77088913/regex-to-find-a-letter-followed-by-5-digits

1条答案

按热度按时间

hgtggwj01#

这是带有适当正则表达式的regexp_substr：字母后面是5位数字。
样本数据：

SQL> with test as
  2  (select '**' col from dual
  3  union all
  4  select '+D91882' from dual
  5  union all
  6  select '3DD12523' from dual
  7  union all
  8  select 'Testing' from dual
  9  union all
 10  select 'A12345' from dual
 11  union all
 12  select '3DR4096' from dual
 13  union all
 14  select 'G92648RE' from dual
 15  union all
 16  select 'D123456' from dual)

查询方式：

17  select col,
 18    regexp_substr(col, '[[:alpha:]][[:digit:]]{5}') result
 19  from test;

COL      RESULT
-------- --------
**
+D91882  D91882
3DD12523 D12523
Testing
A12345   A12345
3DR4096
G92648RE G92648
D123456  D12345

8 rows selected.

SQL>

赞(0）回复(0）举报 2023-10-16

我来回答

oracle 正则表达式查找后跟5位数字的字母

1条答案

相关问题

热门标签

最新问答