UpperCase Replace(Split_Part())

Question

Community, I need assistance with removing the UNDER SCORES '_' and make the name readable first name letter UpperCase last name UpperCase, while removing the number as well. Hope this makes sense. I am running Presto and using Query Fabric. I there a better way to write this syntax?

Email Address
Full_Metal_Jacket@movie.com
TOP_GUN2@movie.email.com

Needed Outcome
Full Metal Jacket
Top Gun

Partical working Resolution:

,REPLACE(SPLIT_PART(T.EMAIL, '@', 1),'_',' ') Name

Something like this:

 ,LOWER(REPLACE(UPPER(SPLIT_PART(T.EMAIL, '@', 1)),'_',' '))Name

Answer 1

Try this:

WITH t(email) AS (
    VALUES 'Full_Metal_Jacket@movie.com', 'TOP_GUN2@movie.email.com'
)
SELECT array_join(
    transform(
        split(regexp_extract(email, '(^[^0-9@]+)', 1), '_'),
        part -> upper(substr(part, 1, 1)) || lower(substr(part, 2))),
    ' ')
FROM t;

How it works:

• extract the non-numeric prefix up to the @ using a regex via regexp_extract
• split the prefix on _ to produce an array
• transform the array by capitalizing the first letter of each element and lowercasing the rest.
• Finally, join them all together with a space using the array_join function.

Update:

Here's another variant without involving transform and the intermediate array:

regexp_replace(
    replace(regexp_extract(email, '(^[^0-9@]+)', 1), '_', ' '),
    '(\w)(\w*)',
    x -> upper(x[1]) || lower(x[2]))

Like the approach above, it first extracts the non-numeric prefix, then it replaces underscores with spaces with the replace function, and finally, it uses regexp_replace to process each word. The (\w)(\w*) regular expression captures the first letter of the word and the rest of the word into two separate capture groups. The x -> upper(x[1]) || lower(x[2]) lambda expression then capitalizes the first letter (first capture group -- x[1]) and lower cases the rest (second capture group -- x[2]).