[MariaDB] 컬럼명 카멜 케이스로 출력하기

 

스네이크 케이스(Snake Case) 컬럼명을 카멜 케이스(Camel Case)로 출력하기

WITH TB AS (
SELECT COLUMN_NAME
     , SUBSTRING_INDEX(SUBSTRING_INDEX(TB.COLUMN_NAME, '_', NUMBERS.N), '_', -1) AS NAME
     , CONCAT('/* ', COLUMN_COMMENT, ' */') AS COLUMN_COMMENT
     , N
  FROM (SELECT 1 AS N UNION ALL 
        SELECT 2 UNION ALL
        SELECT 3 UNION ALL 
        SELECT 4 UNION ALL
        SELECT 5) AS NUMBERS
 INNER JOIN INFORMATION_SCHEMA.COLUMNS TB 
    ON CHAR_LENGTH(TB.COLUMN_NAME) - CHAR_LENGTH(REPLACE(TB.COLUMN_NAME, "_", "")) >= NUMBERS.N - 1
 WHERE TB.TABLE_SCHEMA = '스키마명'
   AND TB.TABLE_NAME = '테이블명'
 ORDER BY COLUMN_NAME, N
)
SELECT COLUMN_NAME
     , COLUMN_COMMENT
     , GROUP_CONCAT(NAME ORDER BY COLUMN_NAME, N SEPARATOR '') AS CAMEL_COLUMN_NAME
  FROM (
    SELECT COLUMN_NAME
         , CONCAT( CASE WHEN N=1 THEN LOWER(LEFT(NAME, 1))
                        ELSE LEFT(NAME, 1)
                    END
                  , LOWER((RIGHT(NAME, LENGTH(NAME)-1)))) AS NAME
         , COLUMN_COMMENT
         , N
      FROM TB
  ) T
 GROUP BY COLUMN_NAME
;

 

 

---

UNION ALL 하는 이유

 

=> 컬럼명에 "_"가 몇개가 있는지 모르는 상태에서 "_"의 개수보다 더 많은 경우, SUBSTRING_INDEX만 사용하면 마지막 문자열을 가져와서 중복될 수 있음

SELECT COLUMN_NAME
     , SUBSTRING_INDEX(COLUMN_NAME, '_', 1) as COLUMN_NAME1
     , SUBSTRING_INDEX(SUBSTRING_INDEX(COLUMN_NAME, '_', 2), '_', -1) as COLUMN_NAME2
     , SUBSTRING_INDEX(SUBSTRING_INDEX(COLUMN_NAME, '_', 3), '_', -1) as COLUMN_NAME3
     , SUBSTRING_INDEX(SUBSTRING_INDEX(COLUMN_NAME, '_', 4), '_', -1) as COLUMN_NAME4
  FROM information_schema.columns a
 WHERE table_schema = '스키마명'
   AND TABLE_NAME = '테이블명'
   AND COLUMN_NAME = 'USE_AT'
 ORDER BY ordinal_position

=>> CHAR_LENGTH(TB.COLUMN_NAME) - CHAR_LENGTH(REPLACE(TB.COLUMN_NAME, "_", "")) 로 _의 개수를 구해 사용

 

SELECT COLUMN_NAME
     , SUBSTRING_INDEX(SUBSTRING_INDEX(TB.COLUMN_NAME, '_', NUMBERS.N), '_', -1) AS NAME
     , CONCAT('/* ', COLUMN_COMMENT, ' */') AS COLUMN_COMMENT
     , N
  FROM (SELECT 1 AS N UNION ALL 
        SELECT 2 UNION ALL
        SELECT 3 UNION ALL 
        SELECT 4 UNION ALL
        SELECT 5) AS NUMBERS
 INNER JOIN INFORMATION_SCHEMA.COLUMNS TB 
    ON CHAR_LENGTH(TB.COLUMN_NAME) - CHAR_LENGTH(REPLACE(TB.COLUMN_NAME, "_", "")) >= NUMBERS.N - 1
 WHERE TB.TABLE_SCHEMA = '스키마명'
   AND TB.TABLE_NAME = '테이블명'
   AND COLUMN_NAME = 'USE_AT'
 ORDER BY COLUMN_NAME, N

 

 

해당 방법 참고 URL

[MySQL] 한 행 안에 있는 문자열 여러 개를 행으로 분리하기