[SOLVED] Snowflake Regex to Camel case file name

Snowflake Regex to Camel case file name

I need to convert filenames stored in a snowflake table to camel case values. I was able to convert it but file extension is also getting Initcapped(.Txt). Please suggest solution (preferably regex).

Source: 'ABC_defg_HiJk_lMn_123.txt'.

Work In Progress: 'Abc_Defg_Hijk_lmn_123.Txt'.

Required: 'Abc_Defg_Hijk_lmn_123.txt'.

SELECT REPLACE(INITCAP(REPLACE(LOWER('ABC_defg_HiJk_lMn_123.txt'),'_',' ')),' ','_');

Solution

You could use a substring operation to separate the filename from the extension. Then, use INITCAP() on the filename alone, and join together at the end with the original extension:

WITH yourTable AS (
    SELECT 'ABC_defg_HiJk_lMn_123.txt' AS filename
)

SELECT
    REPLACE(INITCAP(REPLACE(LOWER(REGEXP_SUBSTR(filename, '[^.]+')), '_', ' ')), ' ', '_')
    ||
    REGEXP_SUBSTR(filename, '\\..+') AS fileout
FROM yourTable;