Presto SQL (Trino) Proper Case, Initcap: How to capitalize the first letter of each word in Presto

Trino (formerly Presto) is missing a commonly used function available in Excel, Hive, Oracle, PLSQL, Db2, python,… here is a fairly easy and efficient way to DIY your way around it.

Morguefile

I desperately needed this but I couldn’t find anything shared out there specific to Trino (Presto is now Trino). I found this to be a fairly straightforward workaround that can work for any string with any number of words. Let’s take a lower case name as an example…

Example table

WITH 
name_table AS
(select ‘jordan lamborn’ as name)

name_table

+ — — — — — — — — +
| name |
+ — — — — — — — — +
| jordan lamborn |
+ — — — — — — — — +

Solved: How to capitalize the first letter of each word in Trino

Original varchar value

‘jordan lamborn’

Code snippet

SELECT(array_join((transform((split(name_table.name,’ ‘)), x -> concat(upper(substr(x,1,1)),substr(x,2,length(x))))),’ ‘,’’))FROM  name_table

Resulting value

‘Jordan Lamborn’

How it works

  1. First, the split() function with space as the delimiter breaks the varchar into an array containing each word separately
    split(name_table.name,’ ‘) >> [“jordan”,”lamborn”]
  2. Next, we’ll use the transform() function to apply a certain treatment to each element of our new array. The way the transform syntax works is that each element of the array becomes an x variable in our new function. I think of this as a For loop in javascript.
    transform((split(name_table.name,’ ‘)), x -> (OUR NEW FUNCTION GOES HERE ))
  3. The first part of our custom function will extract the first character (position 1 substring) of the word x, then capitalize it with upper()
    jordan’ = x >> upper(substr(x,1,1)) >> ‘J’
  4. The second part of our custom function will extract the rest of the word using substr() again and also the length() of x
    ‘jordan’ = x >> substr(x,2,length(x)) >> ‘ordan’
  5. So now that we have ‘J’ and ‘ordan’ after being given ‘jordan’. We can concatenate these two strings together to make ‘Jordan’, which is what we want. This piece simply takes step 3 and step 4 above and slaps them together with a comma in between them.
    concat(
    upper(substr(x,1,1)), substr(x,2,length(x))
    )
  6. Remember, this is all happening inside the transform() but this leaves us with an array. We’ve gone from [“jordan”,“lamborn”] to [“Jordan”,“Lamborn”].
    (transform((split(name_table.name,’ ‘)), x -> concat(upper(substr(x,1,1)),substr(x,2,length(x)))))
  7. The array_join() function will allow us to convert our array to a varchar. We just need to define the delimiter and the null replacement. The delimiter will be the same delimiter we used to break the varchar up . The null replacement will be empty.
    array_join(STEP 6 ARRAY CODE, ‘ ‘ ,’’)

So no matter how many words are in each string, the transform() will apply this concatenation/substring function to each word and spit out a new array. From there we squish the array back into a varchar using array_join().

What do you think? I’d love to hear your thoughts.

Product Manager @ Slice. Curious. Learning, read/, write on product management. ex-Expedia. Product School. PSPO I. https://www.linkedin.com/in/jordanlamborn/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store