Regular Expression patterns in Oracle SQL developer: include numbers

user_13707449 0
Viewed 717

I am trying to find the pattern using oracle sql developer. Include numbers that are "x0000"," 0000",".0000"

AND REGEXP_INSTR(string=>FTP.TEXT, pattern=>'[x]\d\d\d\d\s', match_parameter=>'i')
oracle-sqldeveloper oracle regex
2 Answers

You can use REGEXP_LIKE as follows:

SQL> --sample data
SQL> with your_data(id,str) as
  2  (select 1, 'x0000' from dual union all
  3  select 2, '.0000' from dual union all
  4  select 3, ' 0000'from dual union all
  5  select 4, '0000' from dual) -- this should not be in the result
  6  -- your query starts from here
  7  select * from your_data
  8  where REGEXP_like(str,'[x| |\.][0-9]{4}','i');

        ID STR
---------- -----
         1 x0000
         2 .0000
         3  0000

SQL>
user_115656290

Use

where REGEXP_LIKE(str,'[x.[:space:]]\d{4}','i')

The [x.[:space:]]\d{4} expression matches x, . or any whitespace, then any four digit characters.

user_113298900
Related