how can i extract a string like : 'peter_________Parker_____may___ ' . and the output will be like: 'Peter_Parker_May'.
-
Please add more sample data along with your SQL code, if you have any.Tim Biegeleisen– Tim Biegeleisen2022-07-07 03:26:50 +00:00Commented Jul 7, 2022 at 3:26
-
i a newbie, so i dont have any code, srrQwerty– Qwerty2022-07-07 03:33:52 +00:00Commented Jul 7, 2022 at 3:33
-
Explain in words what you are trying to accomplish. My guess is that you want to replace 2 or more consecutive _ characters with a single _ character and then remove any trailing _ characters. But that's a guess. My guess is that the changes in capitalization between your input and output are unintentional. Maybe you intend the _ in your sample data to represent an arbitrary character and you want to extract just the three substrings "peter", "parker", and "may" and capitalize the first letter in the output.Justin Cave– Justin Cave2022-07-07 04:40:16 +00:00Commented Jul 7, 2022 at 4:40
-
yes, the _ characters just stand for the 'space' characters and the output will remove any nesscesary 'space' characterQwerty– Qwerty2022-07-07 04:53:39 +00:00Commented Jul 7, 2022 at 4:53
Add a comment
|
2 Answers
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_replacereplaces all occurrences of one (or more) underscore characters with a single underscore character. - Function
trimremoves trailing underscore characters. - Function
initcapcapitalizes 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
Comments
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>
