question Re: SQL statement to capitalize the first letter in Support Questions

SQL statement to capitalize the first letter

Sam7 — Thu, 07 Oct 2021 15:37:39 GMT

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

Re: SQL statement to capitalize the first letter

balajip — Mon, 11 Oct 2021 07:57:38 GMT

@Sam7

Was the query working fine before in das ?

Re: SQL statement to capitalize the first letter

Sam7 — Mon, 11 Oct 2021 08:17:35 GMT

What is das?

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

Re: SQL statement to capitalize the first letter

ask_bill_brooks — Mon, 11 Oct 2021 15:49:39 GMT

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

Re: SQL statement to capitalize the first letter

Sam7 — Mon, 11 Oct 2021 15:52:43 GMT

Cheers, I did not see that.

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

Re: SQL statement to capitalize the first letter

ask_bill_brooks — Mon, 11 Oct 2021 23:52:02 GMT

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

Re: SQL statement to capitalize the first letter

BennyZ — Mon, 18 Oct 2021 13:06:01 GMT

With hive query, please try

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

Re: SQL statement to capitalize the first letter

smdas — Thu, 21 Oct 2021 17:32:43 GMT

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

Re: SQL statement to capitalize the first letter

Sam7 — Thu, 04 Nov 2021 11:37:32 GMT

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

Re: SQL statement to capitalize the first letter

BennyZ — Fri, 05 Nov 2021 13:34:26 GMT

@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

Re: SQL statement to capitalize the first letter

Sam7 — Fri, 05 Nov 2021 14:50:24 GMT

Thank you