[MSSQL] 문자열 숫자 변환

소개

6개월차 신입 데이터 분석가입니다. SQL에 대해 조금 더 깊게 이해하고자 공부를 시작합니다. 

 

gibles-deepmind.tistory.com/5

 

글의 목표

1. 문자열 데이터를 숫자열로 바꿔봅시다.
2. 바꾸는 과정에서 발생할 수 있는 문제점에 대해 생각해 봅시다.
3. 해결책에 대해서 생각해봅시다.

---

1. 문자열 숫자열 변환

-- 문자열에 공백만 있는 경우
create table ex (
	string VARCHAR(1000)
);

insert into ex (string) values ('');
insert into ex (string) values ('12');
insert into ex (string) values ('234');
insert into ex (string) values ('345');

select *
from ex

숫자가 아닌 형태의 문자열 중에 공백만 있는 샘플 테이블을 생성했다.

 

 

자 이제 문자열을 숫자형으로 바꿔보자.

변환은 두 가지 형태로 가능하다. 하나는 int, 다른 하나는 bigint이다. big이 "크다"라는 뜻인것처럼 bigint는 int보다 더 큰 크기의 숫자를 저장할 수 있다. 일련번호 처럼 자릿수가 큰 숫자는 bigint로 저장한다. 여기서는 간단하게 int로 변환해볼 것이다.

-- #1 문자열을 숫자열로 바꾸기

-- 1-1.
select cast(string as int) + 1
from ex;

-- 1-2.
select case when string = '' then 0 else cast(string as bigint) end as string_to_int1
into #case1
from ex;

select string_to_int1 + 1
from #case1;

-- 1-3 . 
select cast(replace(string, '', '0')  as int) as string_to_int2
into #case2
from ex;

select string_to_int2 + 1
from #case2;

 

위와 같이 세 가지 방식으로 숫자형 변환이 가능하다. 사실, 굳이 형변환을 안하고 공백만 대체해줘도 된다.

왜 이렇게 쉽게 변환이 될까?

왜냐하면, MSSQL에서는 '' 공백과 NULL을 구분하기 때문이다. 공백은 그 자체로 하나의 값을 가지며 공백을 숫자형으로 변환하면 0이 된다.

select cast('' as int)
select cast(' ' as int)
select cast('  ' as int)

몇 칸이든 상관없다. 공백을 숫자형으로 변환하면 0이 된다. 즉, 데이터에 '' 공백 외에 다른 문자열이 존재하지 않을 경우 이 데이터를 숫자형으로 변환하는 건 매우 간단한 일이다.

---

2. 문자열을 숫자열로 바꾸면서 발생할 수 있는 문제점

-- #2 공백 외에 다른 문자열도 있는 경우
create table ex2 (
	string VARCHAR(1000)
);

insert into ex2 (string) values ('');
insert into ex2 (string) values ('12');
insert into ex2 (string) values ('칼퇴');
insert into ex2 (string) values ('234');
insert into ex2 (string) values ('345');

위와 같이 공백 외에 다른 문자열이 있는 샘플 데이터를 만들었다.

select cast(string as bigint)
from ex2

에러 두두등장

아까와 같은 방식으로 변환하려고 하니 에러가 나온다(bigint인지 int인지는 무관). 왜냐하면, "칼퇴"라는 문자열이 있기 때문이다.

 

3. 해결책

select distinct string
	 , ISNUMERIC(string) checkNum
from ex2

ISNUMERIC은 숫자인지를 체크하는 함수이다. 값이 숫자이면 1, 문자열이면 0을 반환한다. 

 

보시다시피 칼퇴와 공백을 모두 0으로 반환했다. 자 기가막힌 아이디어가 떠오르지 않는가?

ISNUMERIC을 써서 0인 값만 체크를 해서 그것만 0으로 변환하면? 되지 않을까?

한번 해보자.

select string
	 , case when (ISNUMERIC(string) = 1) then string
                else 0
	   end as v
from ex2

짠, 아주 예쁘게 문자열들이 숫자로 변환된 것을 알 수 있다.

REFERENCE

stackoverflow.com/questions/37478423/cast-convert-empty-string-to-int-in-sql-server

CAST/CONVERT empty string to INT in SQL Server

stackoverflow.com/questions/14636282/sql-how-to-convert-varchar-to-bigint

SQL, How to convert VARCHAR to bigint?