我想从具有额外数字和字符串((925467,klm25467,-725467,y25467)的数据的字符串中搜索5位数(如25467)在不同的列行。我希望每行只有25467个结果。
谢谢。
由于您的数字出现在字符串的末尾,因此获取字符串的最后5个字符:
SELECT value,
SUBSTR(value, -5) AS number_value
FROM table_name
对于样本数据:
CREATE TABLE table_name (value) as
select '925467' from dual union all
select 'klm25467' from dual union all
select '-725467' from dual union all
select 'y25467' from dual;
输出:NUMBER_VALUE 25467254672546725467
我想你是在要求一个正则表达式函数:
with test1 as
(
select '925467' digit from dual union all
select 'klm25467' digit from dual union all
select '-725467' digit from dual union all
select '-725467ff' digit from dual union all
select '-725467s' digit from dual union all
select 'y25467' digit from dual
)
select regexp_substr(digit,'[2]{1}[0-9]{3}[7]{1}') from test1
结果将显示字符串中以数字2开始,以数字7结束且有5位数字的所有数字。
结果:
25467
25467
25467
25467
25467
25467