Skip to main content
Sam7
Sam7
Silver ⭐️⭐️
October 7, 2021
Solved

SQL statement to capitalize the first letter

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

smdas
smdasAnswer
Gold ⭐️⭐️
October 21, 2021

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 in thread

10 replies

balajip
Gold ⭐️
balajip
Gold ⭐️
October 11, 2021

@Sam7 

Was the query working fine before in das ? 

 

Translate
Sam7
Sam7Author
Silver ⭐️⭐️
October 11, 2021

What is das?

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

Translate
ask_bill_brooks
Platinum ⭐️
ask_bill_brooks
Platinum ⭐️
October 11, 2021

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

 

 

Translate
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.
BennyZ
Gold ⭐️
BennyZ
Gold ⭐️
October 18, 2021

With hive query,  please try 

 

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

Translate
    smdas
    Gold ⭐️⭐️
    smdasAnswer
    Gold ⭐️⭐️
    October 21, 2021

    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

    Translate
    Sam7
    Sam7Author
    Silver ⭐️⭐️
    November 4, 2021

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

     

    Translate
    BennyZ
    Gold ⭐️
    BennyZ
    Gold ⭐️
    November 5, 2021

    @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

    Translate
    Terms & ConditionsAccessibility statement