varchar number starting with +

I am trying below to find out if the column (dis_num) value is numeric or not which is working fine.

REGEXP_LIKE(dis_num, '^[[:digit:]]+$')

Now dis_num column can starts with + and then numbers like [phone]. How to modify above regex which is consider starting with + as well ? Means if column has number starting with + then also we need to consider as numeric.

Thanks

Answer

If you want to check for a literal + sign you can escape it; and can make it optional with ?

REGEXP_LIKE(dis_num, '^+?[[:digit:]]+$')

Very quick demo:

with t (dis_num) as (
  select '1234' from dual
  union all select 'abc' from dual
  union all select '[phone]' from dual
)
select dis_num,
  case when REGEXP_LIKE(dis_num, '^[[:digit:]]+$') then 'Yes' else 'No' end as check1,
  case when REGEXP_LIKE(dis_num, '^+?[[:digit:]]+$') then 'Yes' else 'No' end as check2
from t;

DIS_NUM     CHE CHE
----------- --- ---
1234        Yes Yes
abc         No  No 
[phone] No  Yes