Oracle SQL: How to capitalize first character of column header rather than the value?

Question

I've built a statement where the result is spooled into a .json file.

With SQLcl, it starts off as:

SET ENCODING UTF-8
SET SQLFORMAT JSON
SPOOL language.json

Then, I do selects. Just an example:

SELECT
    INITCAP(ltl.language) language,

// ... goes on

Output:

"language":"En-Us"

This will capitalize the value of language -- however, what if I want language to be Language in the .json dump?

What I have tried is the following:

SELECT
    ltl.language INITCAP(language),

// ... goes on

But that isn't a valid query.

Also, changing language to Language in the query makes no difference - it's transformed to lowercase:

SELECT
    INITCAP(ltl.language) Language,

Output:

"language":"en-US"

My desired output is:

Output:

"Language":"en-US"

How do I achieve that from the query?

Answer 1

You cannot influence the case of the json value pair KEYS generated when using /*json*/ or SQLFORMAT json. Per our Oracle spec, those are lowercase by design.

You would need to build your own JSON generator, or write some shell scripts with RegEX or whatever you find handy to go in and init cap the keys after-the-fact.

Answer 2

You can use regexp_replace() function.

I don't know how you are composing your final JSON payload, but the workaround would be something like this:

select regexp_replace('"language":"en-US"', '[a-z]', upper(substr('"language":"en-US"', 2, 1)), 1, 1, 'i') 
from dual ; 


SQL> select regexp_replace('"language":"en-US"', '[a-z]', upper(substr('"language":"en-US"', 2, 1)), 1, 1, 'i') language from dual

LANGUAGE
------------------
"Language":"en-US"

SQL>

This implementation assumes that the first letter is the one you want to convert but this letter is after an expected double quote character. (second place in the string)

Regards