Oracle replace all characters before "." dot-CodePudding

I need to replace all characters with nothing before the . character and also replace all [ and ] with nothing. Please see examples below:

from	to
`[PINWHEEL_ASSET].[MX5530]`	`MX5530`
`[PINWHEEL_TRADE].[AR5403]`	`AR5403`

The parts before and after the . dot are variables.

CodePudding user response：

with
  sample_data (my_string) as (
    select '[PINWHEEL_ASSET].[MX5530]' from dual
  )
select rtrim(substr(my_string, instr(my_string, '.')   2), ']') as second_part
from   sample_data
;




SECOND_PART
-----------
MX5530

This assumes that the input string looks exactly like this: [first].[second], where "first" and "second" are (possibly empty) strings that do not contain periods or closing brackets.

CodePudding user response：

Yet another option is to use regular expressions (see line #6).

Sample data:

SQL> with test (col) as
  2    (select '[PINWHEEL_ASSET].[MX5530]' from dual union all
  3     select '[PINWHEEL_TRADE].[AR5403]' from dual
  4    )

Query begins here:

  5  select col,
  6         regexp_substr(col, '\w ', 1, 2) result
  7  from test;

COL                       RESULT
------------------------- --------------------
[PINWHEEL_ASSET].[MX5530] MX5530
[PINWHEEL_TRADE].[AR5403] AR5403

SQL>