13

I need a way to convert a column value to CamelCase with Oracle 10g. I prefer to do it in-line with my select statement but if I have to use a function, that is OK too.

I don't need to support underscores, just spaces.

Thanks

Ayyoudy
  • 3,641
  • 11
  • 47
  • 65
  • I'm not sure I understand the question. If you have stored a value in, say, all lower case in a table, how would any function know how to convert that into CamelCase? That function, at a minimum, would need to have a dictionary of all the available words in order to figure out that the string `camelcase` should be converted into `CamelCase`. And if any of the words in your string are not dictionary words, life gets more challenging. – Justin Cave Sep 04 '12 at 15:01
  • 2
    @JustinCave: You really didn't understand the question :-) "camel case" -> "CamelCase". Note the space between "camel" and "case". That's the indicator. – Daniel Hilgarth Sep 04 '12 at 15:03
  • As far as I am aware there is no built in function that does this. What have you tried to achieve this? – Daniel Hilgarth Sep 04 '12 at 15:03
  • OK. So is your intention that you simply remove every space from the string and capitalize the first character of the string along with any letter that followed a space? – Justin Cave Sep 04 '12 at 15:07
  • @DanielHilgarth, yes you are right. "camel case" -> "CamelCase" is what I am trying to achieve. I am aware that there is no built in function, so I am looking for a way to do it... I have tried a couple of things but it is getting messy and was hoping for something cleaner – Ayyoudy Sep 04 '12 at 15:07
  • @JustinCave, yes that's the idea. Capitalize the first letter of the string.. capitalize every letter following a space and un-capitalize (lower-case) all other characters... – Ayyoudy Sep 04 '12 at 15:09

2 Answers2

43

I guess a combination of initcap() and replace() would work:

select replace(initcap('hi ben'),' ') from dual;

REPLA
-----
HiBen

This simply capitalises the first character of every word and then replaces the spaces with nothing.

It obviously won't work if the first character is numeric:

select replace(initcap('go 2stack overflow'),' ') from dual;

REPLACE(INITCAP(
----------------
Go2stackOverflow
Ben
  • 51,770
  • 36
  • 127
  • 149
  • That is AWESOME! I wasn't aware of the initcap built-in function! Works like charm... Thank you – Ayyoudy Sep 04 '12 at 15:19
3

That's not my understanding of camelCase

select substr(lower('Camel Case means the first char should be lower cased'),1,1)||substr(replace(initcap('Camel Case means the first char should be lower cased'),' '),2) from dual;

camelCaseMeansTheFirstCharShouldBeLowerCased                                    
1 row selected.
N3R4ZZuRR0
  • 2,400
  • 4
  • 18
  • 32
Dave the Humanoid
  • 31
  • 1