Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Regular expression to add undesrcore before single capital

6363Jun 16 2011 — edited Jun 16 2011
I am trying to convert the names of attributes that use capitalization sort of like camelcase to distinguish multiple words, e.g. VehicleColor to use underscores instead, eg. Vehicle_Color.

I have a regular expression that does this, however I have a problem when an abbreviation consisting of multiple upper case characters is present, e.g. AverageMPG becomes Average_M_P_G. I am trying to come up with a pattern that only adds the underscores to the first occurrence of a capital letter in a series which should result in the abbreviation MPG becoming Average_MPG.
SQL> select * from v$version where rownum = 1;

BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

SQL> with test_data as
  2      (
  3      select 'VehicleColor' str from dual union all
  4      select 'WeightClass' str from dual union all
  5      select 'AverageMPG' str from dual union all
  6      select 'HighMPG' str from dual union all
  7      select 'LowMPG' str from dual union all
  8      select 'ABS_System' str from dual
  9      )
 10  select
 11      str,
 12      regexp_replace(str, '([A-Z])', '_\1', 2) result
 13  from
 14      test_data;

STR          RESULT
------------ ------------------------
VehicleColor Vehicle_Color
WeightClass  Weight_Class
AverageMPG   Average_M_P_G
HighMPG      High_M_P_G
LowMPG       Low_M_P_G
ABS_System   A_B_S__System

6 rows selected.

SQL>
These are the results I would like, but I don't know how to modify the pattern to only have the replace act on the first capital letter in a series of capitals or if it is possible.
STR          RESULT
------------ ------------------------
VehicleColor Vehicle_Color
WeightClass  Weight_Class
AverageMPG   Average_MPG
HighMPG      High_MPG
LowMPG       Low_MPG
ABS_System   ABS_System  
This post has been answered by Frank Kulash on Jun 16 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 14 2011
Added on Jun 16 2011
6 comments
1,133 views