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