How to convert only first letter uppercase without using Initcap in Oracle?

Question

Is there a way to convert the first letter uppercase in Oracle SQl without using the Initcap Function?

I have the problem, that I must work with the DISTINCT keyword in SQL clause and the Initcap function doesn´t work.

Heres is my SQL example:

select distinct p.nr, initcap(p.firstname), initcap(p.lastname), ill.describtion
from patient p left join illness ill
  on p.id = ill.id          
where p.deleted = 0
order by p.lastname, p.firstname;

I get this error message: ORA-01791: not a SELECTed expression

Solution 1

When SELECT DISTINCT, you can't ORDER BY columns that aren't selected. Use column aliases instead, as:

select distinct p.nr, initcap(p.firstname) fname, initcap(p.lastname) lname, ill.describtion
from patient p left join illness ill
  on p.id = ill.id          
where p.deleted = 0
order by lname, fname

Solution 2

this would do it, but i think you need to post your query as there may be a better solution

select upper(substr(<column>,1,1)) || substr(<column>,2,9999) from dual

Solution 3

To change string to String, you can use this:

SELECT
regexp_replace ('string', '[a-z]', upper (substr ('string', 1, 1)), 1, 1, 'i')
FROM dual;

This assumes that the first letter is the one you want to convert. It your input text starts with a number, such as 2 strings then it won't change it to 2 Strings.

Comments

[Aleksej]

What's the problem with DISTINCT? I see no problems in DISTINCT with InitCap; can you please post your code?

[Admin]

I have edit my question

[jarlh]

When SELECT DISTINCT, you can't ORDER BY columns that aren't selected.

[Admin]

But without initcap function it works great!

[Aleksej]

It's not InitCap, is the fact that you apply a function to the field you use for ordering; even substr or any other function will give the same problem.

[Admin]

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.