How to capitalize the first letter of words in Snowflake?

user_15268236 0
Viewed 1408

I need to capitalize the first letter of certain words in my SQL qwery in Snowflake.

I am currently using this function:

SELECT ...
    case when FLAG1 is null then upper(FLAG2) else FLAG1 END as STATUS,
...;

The UPPER() function however renders all my FLAG2 words to be fully capitalized. Anyone know how to capitalize the first letter of words in Snowflake?

snowflake-cloud-data-platform sql string
2 Answers

Use initcap() function, it returns the input string (expr) with the first letter of each word in uppercase and the subsequent letters in lowercase.

If you want only first letter in a string to be capitalized, ignoring word delimiters (i.e. the input expression is treated as a single, continuous word), specify empty string as delimiters parameter

INITCAP( str, '')
user_27003440 
SELECT column1, IFF(column1, UPPER(SUBSTRING(column2,1,1)) || LOWER(SUBSTRING(column2,2)), column2) 
FROM VALUES 
    (true,'all_lower'),
    (true, 'ALL_UPPER'),
    (true, 'mIxEd'),
    (false,'all_lower'),
    (false, 'ALL_UPPER'),
    (false, 'mIxEd');

which can be unpacked a little to see the parts at work.

SELECT column1 as flag
     ,column2 as orig
     ,UPPER(SUBSTRING(column2,1,1)) || LOWER(SUBSTRING(column2,2)) as first_upper
     ,IFF(flag, first_upper, orig)
FROM VALUES 
    (true,'all_lower'),
    (true, 'ALL_UPPER'),
    (true, 'mIxEd'),
    (false,'all_lower'),
    (false, 'ALL_UPPER'),
    (false, 'mIxEd');

which gives:

FLAG    ORIG        FIRST_UPPER   IFF(FLAG, FIRST_UPPER, ORIG)
TRUE    all_lower   All_lower     All_lower
TRUE    ALL_UPPER   All_upper     All_upper
TRUE    mIxEd       Mixed         Mixed
FALSE   all_lower   All_lower     all_lower
FALSE   ALL_UPPER   All_upper     ALL_UPPER
FALSE   mIxEd       Mixed         mIxEd
user_439920
Related