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

2
votes

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

4
sqloracle
What's the problem with DISTINCT? I see no problems in DISTINCT with InitCap; can you please post your code? - Aleksej
I have edit my question - user2504767
When SELECT DISTINCT, you can't ORDER BY columns that aren't selected. - jarlh
But without initcap function it works great! - user2504767
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. - Aleksej

4 Answers

6
votes

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
1
votes

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
1
votes

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.

0
votes

You can also use the column number instead of the name or alias:

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 3, 2;