sql - Snowflake Regex to Camel case file name - Stack Overflow

admin管理员组

文章数量:1313598

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'),'_',' ')),' ','_');

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'),'_',' ')),' ','_');

• sql
• snowflake-schema

Share Improve this question Follow edited Jan 30 at 23:04 BabaGee asked Jan 30 at 23:01 BabaGeeBabaGee 1111 silver badge55 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

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;

you can split based on . and perform the operations and then concat the extension.

WITH test AS (
    SELECT 'ABC_defg_HiJk_lMn_123.txt' AS filename
)
,splitt as 
( SELECT 
    SPLIT_PART(filename, '.', 1) AS name_part,
    SPLIT_PART(filename, '.', 2) AS extension_part
FROM 
    test)
select REPLACE(INITCAP(REPLACE(LOWER(name_part),'_',' ')),' ','_')  ||'.' || extension_part as camel_case
from splitt ;

本文标签： sqlSnowflake Regex to Camel case file nameStack Overflow