I have this example table:

what should i use to get exact number between '#', like this:

i've no clue to try code to, please help, thanks

CodePudding user response：

As you said - between two # signs:

SQL> with test (id, description) as
  2    (select 'AD112', '#21#'               from dual union all
  3     select 'AD122', '#8970#'            from dual union all
  4     select 'AD124', 'NOT ALL SENT#115#' from dual
  5    )
  6  select id,
  7    substr(description, instr(description, '#', 1, 1)   1,
  8                        instr(description, '#', 1, 2) - instr(description, '#', 1, 1) - 1
  9          ) description
 10  from test
 11  order by id;

ID    DESCRIPTION
----- -----------------
AD112 21
AD122 8970
AD124 115

SQL>