WITH t AS ( SELECT '1400' v UNION ALL SELECT '132' UNION ALL SELECT '0.12' UNION ALL SELECT '1420.1356' UNION ALL SELECT '-1340' UNION ALL SELECT '-3.1354' UNION ALL SELECT '-1320.23' ) SELECT v , TRIM(TRAILING '.' FROM -- 오른쪽 . 제거 TRIM(TRAILING '0' FROM -- 오른쪽 0 제거 FORMAT(v, 5) -- 3자리 컴마 및 소수점 5자리 채우기 )) x FROM t ;
-- 5.7 이하 버전 SELECT str, CASE WHEN str REGEXP '^-?[0-9]+(\\.[0-9]+)?$' THEN concat(format(CAST(str AS signed), 0), substring(str, instr(str, '.'))) ELSE str END as cast_str FROM ( SELECT 'a2' AS str UNION ALL SELECT '2a' AS str UNION ALL SELECT '123.4.123' AS str UNION ALL SELECT '1234.' AS str UNION ALL SELECT '1' AS str UNION ALL SELECT '1000' AS str UNION ALL SELECT '0.1234' AS str UNION ALL SELECT '1234.56' AS str UNION ALL SELECT '-1234.00' AS str ) a;