Support Questions
Find answers, ask questions, and share your expertise

SQL statement to capitalize the first letter

New Contributor

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

Accepted Solutions

Cloudera Employee

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

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

Cloudera Employee

@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

Cloudera Employee

@Sam7 

Was the query working fine before in das ? 

 

New Contributor

What is das?

This is the first time I am using this code, very new to Hue

@Sam7 DAS is Data Analytics Studio, which was the label you originally applied to your post. See the following partial page shot:

Partial page shot of community post for responding to 11 Oct captured 2021-10-11_08-18-03.jpg

So that is why @balajip was asking if the query was working fine before you made the latest change to capitalize the initial letter.  DAS is a completely different tool than Hue.

 

It would help members of the community in offering possible answers to your question if you could post a screen shot of the query you're submitting in the actual tool you are using as well as the error message that tool is returning when it attempts to execute the query.

 

 

Bill Brooks, Community Moderator
Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.

New Contributor

Cheers, I did not see that.

 

Can I remove a tag or shall I start again with my question. 

 

@Sam7 No, you don't have to start a new question, just go ahead and post your screen shot of the query in Hue and the resulting error message and I'll revise the label for you if it's necessary. Also, let us know what distribution you're using and what version of Hue you're running, that would help us help you a lot.

 

 

Bill Brooks, Community Moderator
Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.

Cloudera Employee

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

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

New Contributor

Thanks for the support. How do I make the second word a capital too?

 

Cloudera Employee

@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