Convert camelCase to snake_case not returning expected output MySQL 8-CodePudding

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'))