SQL statement to capitalize the first letter

[#1]

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

[#2 · Accepted solution]

With hive query,  please try 

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

[#3 · Accepted solution]

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:

As such, We are marking the Post as Solved. 

Regards, Smarak

[#4]

@Sam7 

Was the query working fine before in das ? 

[#5]

What is das?

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

[#6]

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

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.

[#7]

Cheers, I did not see that.

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

[#8]

@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.

[#9 · Accepted solution]

With hive query,  please try 

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

[#10 · Accepted solution]

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:

As such, We are marking the Post as Solved. 

Regards, Smarak

[#11]

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

[#12 · Accepted solution]

@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