Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

SQL statement to capitalize the first letter

avatar
Explorer

Hi,

 

I want the first letter to be a capital letter and the rest lower case:

 

UPPER(LEFT(fieldname,1))+LOWER(SUBSTRING(fieldname,2,LEN(fieldname))) AS fieldname

 

I keep getting this error:

Error while compiling statement: FAILED: ParseException line 25:10 cannot recognize input near 'LEFT' '(' 'fieldname' in function specification

 

Please help

3 ACCEPTED SOLUTIONS

avatar
Expert Contributor

With hive query,  please try 

 

select concat(UPPER(SUBSTRING(fieldname,1,1)),LOWER(SUBSTRING(fieldname,2,LENGTH(fieldname)))) AS fieldname from ...

View solution in original post

avatar
Super Collaborator

Hello @Sam7 

 

We hope the SQL shared by @BennyZ helped your Team meet the Use-Case. Showing the SQL Output from the SQL shared in a Sample Table:

 

Screenshot 2021-10-21 at 11.00.26 PM.png

 

As such, We are marking the Post as Solved. 

 

Regards, Smarak

View solution in original post

avatar
Expert Contributor

@Sam7 please try :

select concat(UPPER(SUBSTRING(fieldname,1,2)),LOWER(SUBSTRING(fieldname,3,LENGTH(fieldname)))) AS fieldname from ...

 

You can check the below document for the usage of substring()

https://cwiki.apache.org/confluence/display/hive/languagemanual+udf

View solution in original post

10 REPLIES 10

avatar
Explorer

Thank you