I have a table in snowflake that looks like this:
--------------------
| fieldname |
--------------------
|thisIsTestOne |
|thisIsTestTwo |
|this_test |
--------------------
I need to convert the PascalCase values in the column to snake_case. Note: I only want to convert them to snake_case if they are PascalCase. The output should look like this;
-------------------- ---------------------
| fieldname | newfieldname |
-------------------- ---------------------
|thisIsTestOne |this_is_test_one |
|thisIsTestTwo |this_is_test_two |
|this_test |this_test |
-------------------- ---------------------
You should be able to use REGEXP_REPLACE
to insert an underscore between a lower-case character followed by an upper-case character, and then LOWER
to convert to lower case i.e.
SELECT LOWER(REGEXP_REPLACE(fieldname, '([a-z])([A-Z])', '\\1_\\2'))
FROM yourtable