SQL 문자 숫자 비교 - SQL munja susja bigyo

문자형을 숫자형으로 변환하기- TO_NUMBER

먼저, Oracle 에서 TO_NUMBER 함수를 사용하지 않아도 문자를 숫자로 인식하고 계산해줍니다.

이것은 Oracle의 특징인데, 데이터 형을 자동으로 변환해주어 상수인지 문자열인지 알아서 구분해준다는 것이죠!

이를 묵시적, 혹은 자동 형 변환이라고 합니다.

결과가 똑같은 것을 확인할 수 있습니다.

하지만 이는 Oracle 에 적용되는 것이고 다른 DB나 언어에서는 적용이 안될 수 있다는 것!

참고해주세요~

참고: KIC 캠퍼스 머신러닝기반의 빅데이터분석 양성과정

[DB-오라클] - 문자와 숫자간의 연산에 대한 고찰

오라클에서의 문자와 숫자연산에 대한 고찰

select '200507'-100 from dual; // 결과는 200507 - 문자열이 아닌 숫자.

위에서 볼 수 있는 것 처럼 문자열과 숫자간의 연산이 변환없이 가능함 '-'연산자가 피연산자를 알맞게 자동형변환함.

단 결과는 문자열이 아닌 숫자.


SQL> select '200507'*100, '200507'/100,'200507'+100,'200507'-100 from dual where '200' = 200;

'200507'*100 '200507'/100 '200507'+100 '200507'-100
------------ ------------ ------------ ------------
20050700 2005.07 200607 200407


사칙연산에 대해 모두 잘작동함을 알 수 있다.


= 문자열과 숫자의 비교연산 =

select 'true' from dual where '200'=200;
select 'true' from dual where '200 '=200;
select 'true' from dual where ' 200 '=200;
select 'true' from dual where ' 200'=200;
select 'true' from dual where ' 00200'=200;
select 'true' from dual where ' 200'=200.0; -- 실수형에 대해서도 성립한다.

위의결과 모두 true를 반환한다.

SQL> select 'true' from dual where ' 0020 0'=200;

select 'true' from dual where ' 0020 0'=200
ORA-01722: invalid number

당연히 나머지 비교연산자에 대해서도 문제 없이 동작한다.

다음검색

현재 게시글 추가 기능 열기

• 북마크
• 공유하기
• 신고하기

댓글

• 댓글 0
• 댓글쓰기
• 답글쓰기

‘D_F6pVu.F1sVw0’ 포함된 새글이 등록될 때 알림 받기

1. 저장소 위치 github.com/GiblesDeepMind/deepsql GiblesDeepMind/deepsql Contribute to GiblesDeepMind/deepsql development by creating an account on GitHub. github.com 본 프로젝트에 사용하는 간단한..

gibles-deepmind.tistory.com

글의 목표

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

I came across a bug where I was using CAST(Col1 AS INT) + CAST(Col2 AS INT) where both Col1 and Col2 are VARCHAR and I was getting valid results out when Col1 or Col2 was blank and I didn't expect ...

stackoverflow.com

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

SQL, How to convert VARCHAR to bigint?

I have a field that is VARCHAR(6) I am trying to insert it into another table of type bigint it is giving me an error (Error Converting from data type varchar to bigint here is what i am doing