CODEWITHSUNDEEP
sqloracle-database
Qwerty
Qwerty

Reputation: 1

extract and print string in oracle

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

Upvotes: 0

Views: 59

Answers (2)

Abra
Abra

Reputation: 20914

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

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

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>

Upvotes: 0

Related Questions