CODEWITHSUNDEEP
sqloracle-database
Imtiaz
Imtiaz

Reputation: 1

SQL - Capitalize every word ignoring capitals

I need to be able to convert something like 

'ABC abc abc Abc'

to 

'ABC Abc Abc Abc'

So the first letter of every word becomes capital and words that have capitals remain the same.

I have tried initcap but this turns the rest of each word into lowercase after it has been capitalized.

select initcap('ABC abc abc Abc') from dual;

Any help would be appreciated.

Upvotes: 0

Views: 169

Answers (1)

Littlefoot
Littlefoot

Reputation: 142778

Here's one option:

SQL> with
  2  test as
  3    (select 'ABC abc abc Abc' col from dual),
  4  tor as
  5    -- convert COL into rows
  6    (select regexp_substr(col, '[^ ]+', 1, level) val
  7     from test
  8     connect by level <= regexp_count(col, ' ') + 1),
  9  capit as
 10    -- choose whether to capitalize first letter or not
 11    (select case when upper(val) = val then val
 12                 else initcap(val)
 13            end cap
 14     from tor)
 15  -- concatenate them back to a string
 16  select listagg(cap, ' ') within group (order by null) result
 17  from capit;

RESULT
--------------------------------------------------------------------------------
ABC Abc Abc Abc

SQL>

Or, a little bit modified:

SQL> with
  2  test as
  3    (select 'ABC abc abc Abc' col from dual)
  4  select listagg(
  5         case when         regexp_substr(col, '\w+', 1, level) =
  6                   upper(  regexp_substr(col, '\w+', 1, level))
  7              then         regexp_substr(col, '\w+', 1, level)
  8              else initcap(regexp_substr(col, '\w+', 1, level))
  9         end, ' ') within group (order by null) res
 10  from test
 11  connect by level <= regexp_count(col, ' ') + 1;

RES
-----------------------------------------------------------------------
ABC Abc Abc Abc

SQL>

Upvotes: 1

Related Questions