I need to search a fix length number from string having same data with extra digit and word using sq-CodePudding

I want to search a 5 digit number (Like 25467) from string having data with extra number and string like ((925467,klm25467,-725467,y25467) in different rows of column. I want only 25467 result in each row.

Thank you.

CodePudding user response：

I think you are asking for a regex function:

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 'y25467' digit  from dual 
)
select regexp_substr(digit,'[0-9]{5}') from test1

Result:

CodePudding user response：

Since your numbers appear at the end of the string, get the last 5 characters of the string:

SELECT value,
       SUBSTR(value, -5) AS number_value
FROM   table_name

Which, for the sample data:

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;

Outputs:

VALUE NUMBER_VALUE

925467 25467

klm25467 25467

-725467 25467

y25467 25467

VALUE	NUMBER_VALUE
925467	25467
klm25467	25467
-725467	25467
y25467	25467

db<>fiddle here