search:

포맷팅된 숫자 문자열을 숫자형으로 변경하기

05 Mar 2014

안내

본 문서는 블로그의 운영자인 본인이 Stackoverflow에 올린 답변을 정리한 글입니다.

Stackoverflow URL

http://stackoverflow.com/questions/20214977/how-to-convert-a-pre-formatted-value-to-a-float/20215140

질문

원래는 DECIMAL(10, 2) 형인 컬럼이 존재하는데 VIEW에 저장하면서 FORMAT(col_name, 2)를 이용하여 숫자 포맷을 변경하고 있다. 따라서 원본 테이블의 “12345.00”이라는 숫자는 VIEW에서는 “12,345.00”으로 변경되어 있다.

이때 VIEW의 포맷팅된 컬럼에 대해서 SUM()을 하는 경우 결과가 잘못 출력된다. VIEW의 포맷팅된 문자열을 다시 DECIMAL(10, 2) 타입으로 변경하고 싶은데 방법이 없을까?

답변

FORMAT() 함수는 숫자의 자리 수를 구분해 주는 함수이다. 3번째 인자를 이용하여 Locale을 지정하면 구분자를 콤마 대신 점으로도 줄 수 있다. 다음 예를 보자.

mysql> SELECT FORMAT('12345.00', 2);
+-----------------------+
| FORMAT('12345.00', 2) |
+-----------------------+
| 12,345.00             |
+-----------------------+
1 row in set (0.00 sec)
 
mysql> SELECT FORMAT('12345.00', 2, 'de_DE');
+--------------------------------+
| FORMAT('12345.00', 2, 'de_DE') |
+--------------------------------+
| 12.345,00                      |
+--------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT FORMAT('12345.00', 2, 'en_US');
+--------------------------------+
| FORMAT('12345.00', 2, 'en_US') |
+--------------------------------+
| 12,345.00                      |
+--------------------------------+

독일의 경우 세자리 구분자가 점이 아니라 콤마가 사용되었다. 포맷팅된 숫자 문자열을 SUM() 함수에 입력하면 결과가 제대로 출력되지 않는다.

mysql> SELECT SUM("12,345.00");
+------------------+
| SUM("12,345.00") |
+------------------+
|               12 |
+------------------+
1 row in set, 1 warning (0.01 sec)
 
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '12,345.00' |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)

따라서 “12,345.00” 같은 문자열을 다시 “12345.00”으로 변환해야 한다. 어떻게 할 수 있을까? 필자가 모르는 다른 방법이 더 있을 수도 있지만 다음과 같이 2가지 방법을 제안했다.

REPLACE() 함수를 두 번 호출하기

“12,345.00”에서 2개의 문자, 즉 콤마와 점을 빈 문자열로 치환해야 한다. 우선 REPLACE()를 두 번 호출하는 방법이 있다.

mysql> SELECT REPLACE(REPLACE('1.123,00', ",", ""), ".", "") AS replaced;
+----------+
| replaced |
+----------+
| 112300   |
+----------+
1 row in set (0.00 sec)

REPLACE()의 결과는 문자형이므로 CAST()를 이용하여 DECIMAL(10, 2)로 변경하는 것이 좋다.

mysql> SELECT CAST(REPLACE(REPLACE('1.123,45', ",", ""), ".", "") AS DECIMAL(10, 2)) AS casted;
+-----------+
| casted    |
+-----------+
| 112345.00 |
+-----------+
1 row in set (0.00 sec)

UDF(User Defined Function)을 이용하는 방법

앞의 REPLACE()를 2번 호출하는 방법도 나쁘진 않다. 그러나 치환해야 할 문자가 수십 개가 넘어가면 쿼리 작성하기가 어려울 것이다. 아쉽게도 MySQL에는 정규 표현식을 이용하여 문자열을 치환하는함수가 없다.

다행이 외부 개발자가 정규 표현식을 사용할 수 있는 UDF를 공개하였다. UDF(User Defined Function)는 MySQL에 부족한 함수를 일반 사용자가 추가할 수 있는 방법이다. UDF는 C 언어를 이용하여 개발되며 공유 라이브러리 (Shared Library) 형태로 컴파일 한 뒤 MySQL에 설치된다.

자세한 것은 https://github.com/mysqludf/lib_mysqludf_preg를 방문하기 바란다.

“MySQL” 카테고리의 추천 글