how can i extract a string like : 'peter_________Parker_____may___ ' . and the output will be like: 'Peter_Parker_May'.

CodePudding user response：

Looks like you want to

• remove superfluous underlines
• remove trailing underline
• capitalize first letter

Then this might be one option:

SQL> with test (col) as
  2    (select 'peter_________Parker_____may___' from dual)
  3  select initcap(rtrim(regexp_replace(col, '_ ', '_'), '_')) result
  4  from test;

RESULT
----------------
Peter_Parker_May

SQL>

CodePudding user response：

The following produces the requested output from the sample input in your question.

select initcap(trim(trailing '_'
                        from regexp_replace('peter_________Parker_____may___',
                                            '_ ',
                                            '_'))) as RESULT
  from DUAL

• Function regexp_replace replaces all occurrences of one (or more) underscore characters with a single underscore character.
• Function trim removes trailing underscore characters.
• Function initcap capitalizes the first character in each word.

Refer to Oracle database documentation, namely Character Functions Returning Character Values in SQL Language Reference

Also refer to this db<>fiddle