How to INITCAP string in QS?

Q&A
, ,
1

Hello,

I was wondering if there’s a way to achieve INITCAP by using the calculated field in QuickSight directly.
e.g. sample to Sample

1 Like
2

Hi @irinaaaaa - There is NO direct INITCAP function available in QS, however you can use combination of substring and to_upper to achieve the same. By saying this, we can request this as feature request as well.

Hi @DylanM @ErikG - Please advise on this.

Regards - Sanjeeb

1 Like
3

Hello @irinaaaaa, we can definitely tag this as a feature request as well, but I will post a function that should lead you to your desired solution if you only ever want the first character to be capital.

convertString = (concat(
toUpper(substring({field_name}, 1, 1)), 
substring({field_name}, 2, strlen({field_name})-1)
))

To explain this, we will split the string into 2 substrings. Convert the first substring of a single character to uppercase, then determine the length of the second string by using the substring function. Then, we can concat them together into a single string value. I’ll mark this work-around as the solution and tag this as a feature request! Let me know if you have any follow-up questions.

4

Thanks, @DylanM, what about the cases when there’s a space between two words? E.g. hello world to Hello World

5

Hello @irinaaaaa, I think at that point you would like want to complete this task in SQL where you could utilize a function like INITCAP. If you really wanted to do it in QuickSight, I suppose you could complete the converString function within an ifelse statement where you would have to check for the presence of a blank string value, i.e. ' ' with the locate function. Check if that value exists then use that value + 1 to add another portion of the concat function.

ifelse(locate({field_name}, ' ') > 0,
concat(
toUpper(substring({field_name}, 1, 1)), 
substring({field_name}, 2, locate({field_name}, ' ')-1),
toUpper(substring({field_name}, locate({field_name}, ' ') + 1, 1),
substring({field_name}, locate({field_name}, ' ') + 2, strlen({field_name}) - (locate({field_name}, ' ')+2)),
concat(
toUpper(substring({field_name}, 1, 1)), 
substring({field_name}, 2, strlen({field_name})-1)
)
)
``

This should be close to what you are looking for, but I would recommend trying to do this in SQL if you can. This still will only account for a string that is 2 words.
6

I see. Thanks a lot, @DylanM for sharing.

1 Like