CODEWITHSUNDEEP
sqloracle-database
user2504767
user2504767

Reputation:

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

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

Upvotes: 3

Views: 29983

Answers (5)

todorp
todorp

Reputation: 1

WITH inData AS
( 
  SELECT 'word1, wORD2, word3, woRD4, worD5, word6' str FROM dual
),
 inRows as
(
  SELECT 1 as tId, LEVEL as rId, trim(regexp_substr(str, '([A-Za-z0-9])+', 1, LEVEL)) as str
  FROM inData
  CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0
)
SELECT tId, LISTAGG( upper(substr(str, 1, 1)) || substr(str, 2) , '') WITHIN GROUP (ORDER BY rId) AS camelCase
FROM inRows 
GROUP BY tId;

Upvotes: 0

RAG
RAG

Reputation: 11

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.

Upvotes: 1

Daniela
Daniela

Reputation: 1

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;

Upvotes: 0

jarlh
jarlh

Reputation: 44696

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

Upvotes: 6

davegreen100
davegreen100

Reputation: 2115

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

Upvotes: 3

Related Questions