I am trying to write a text transformation in a SELECT
statement to convert variable names from camelCase to upper SNAKE_CASE. I'm on MySQL v.8.0.26.
For example: thisIsTheVar
should become THIS_IS_THE_VAR
I followed this answer, but the solution has parameters that my version of MySQL says are invalid (the 'g') (Edit: This solution was for Postgres, but I wanted to document what I had tried).
After some reading, I wrote this:
SELECT UPPER(CONCAT(SUBSTRING('thisIsTheVar', 1, 1),
REGEXP_REPLACE(SUBSTRING('thisIsTheVar', 2), '([A-Z])', '_\\1')));
But this returns:
T_1_1_1_1_1_1_1_1_1_1_1
Anything obvious that I'm missing?
CodePudding user response:
In MySQL, use $1
instead of \1
as a backreference, and it needs parentheses around the pattern to be referenced.
upper(regexp_replace('thisIsTheVar' collate utf8mb4_bin, '([A-Z])', '_$1'))