Trying to change strings in a column (Snowflake sql)

Question

I have a column in my table in Snowflake called "A". In this column I have values such as "groceryStore", "postOffice", "nordstromRackStore" etc. The values are currently in camel case but I would like to change them so that they follow the format "GROCERY_STORE", "POST_OFFICE", "NORDSTROM_RACK_STORE" and so on. Is there a way to change the values to be all uppercase and have the words separated by an underscore in this? I'm not sure how to use regex for camel case format. Or even alternatively, is there a way to change the result format into camel case? As in change "GROCERY_STORE" to "groceryStore"? Thanks!

Answer 1

It (REGEXP_REPLACE), seems to work, unless I am missing something in the question.

Original data -

with cte(col1) as (
 select * from values
 ('nordstromRackStore'),('groceryStore'),('postOffice'),('thisIsComplexCamelCaseAndLongToo')
)SELECT * from cte;

COL1
nordstromRackStore
groceryStore
postOffice
thisIsComplexCamelCaseAndLongToo

After replace -

with cte(col1) as (
  select * from values
 ('nordstromRackStore'),('groceryStore'),('postOffice'),('thisIsComplexCamelCaseAndLongToo')
)SELECT upper(regexp_replace(col1,'([A-Z])', '_\\1', 2)) as snake_case_col from cte;

SNAKE_CASE_COL
NORDSTROM_RACK_STORE
GROCERY_STORE
POST_OFFICE
THIS_IS_COMPLEX_CAMEL_CASE_AND_LONG_TOO