신기한 연구소

안녕하세요. 신기한 연구소입니다.

SQL 작업을 하다 보면 NULL 처리가 필요한 경우가 많은데요.

SOL에서 NULL 처리를 하는 방법에 대해 알아봅니다.

오라클 기준으로 같이 살펴보겠습니다.

우선 NULL에 대해 알아볼게요.

NULL은 아직 정의가 안되었다, 또는 배정이 안 된 상태를 말합니다.

빈 값(공백)이나 0과는 다른 값이므로 주의해야 합니다. ASCII값도 다르거든요.

NULL의 특징 중 하나는 해당 값과 연산(+,-,*,/)을 해도 무조건 NULL이 나온다는 겁니다.

NULL에 대한 함수를 살펴보겠습니다.

우선 우리가 SELECT 문을 통해 데이터를 조회했는데 특정 컬럼의 값이 NULL이 나오는 경우가 있습니다.

이런 경우 대체할 값을 넣으면 좋은데요.

NVL이라는 함수를 사용하면 됩니다.

NVL(표현식_1, 표현식_2)

표현식_1의 값이 NULL이면 표현식 2의 값을 보여주고 NULL이 아니면 표현식_1의 값을 그대로 보여주는 함수입니다.

먼저 dept테이블의 loc 컬럼에 NULL값을 넣고 입력합니다.

조회하면 NULL로 표시됩니다.

아직 값이 할당 안되었다는 의미입니다.

사용자 화면에 NULL로 표시되면 일반 사용자들은 무슨 의미인지 모르겠지요?

Loc가 NULL인 경우는 ‘미설정’으로 변경해서 사용자에게 보여줄 수 있습니다.

5번째 라인에 NULL값이 '미설정'으로 변경된 것을 확인할 수 있습니다.

다음으로 NULLIF()에 대해 살펴봅니다.

NULLIF(표현식_1, 표현식_2)

표현식_1의 값과 표현식_2의 값이 같으면 NULL을 반환하는 함수입니다.

5번째 loc값과 NULL이 같기에 결과가 NULL로 나왔습니다.

3번째 dname값이 'SALES'면 NULL을 반환한다는 표현이기에 NULL로 변경된 것을 확인할 수 있습니다.

마지막으로 하나 더 살펴볼 함수는 COALESCE입니다.

COALESCE(표현식_1, 표현식_2, …)

표현식_1의 컬럼 중 NULL이 아닌 첫 번째 값을 보여줍니다.

표현식_2도 같은 원리이며 원하는 만큼 컬럼을 지정하면 됩니다.

coalesce(loc, dname)으로 표현식을 만들었는데 5번째 loc값이 NULL이어서 그다음 dname값인 IT가 나온 것을

확인할 수 있습니다.

AAA, BBB, CCC 컬럼값을 확인해 보면

ANO가 2인 경우 AAA는 NULL,

ANO가 3인 경우 AAA, BBB는 NULL입니다.

COALESCE를 사용하면 1행은 AAA가 값이 있으니 해당 값인 1

2행은 AAA가 NULL이기에 패스~ BBB는 값이 있으니 해당 값인 2

3행은 AAA, BBB가 NULL이기에 패스~ CCC는 값이 있으니 해당 값인 3

이 표출됩니다.

마지막으로 실전에서도 자주 사용하는 공집합에 대해 알아봅니다.

deptno가 60인 행은 없습니다.

그럼 데이터 자체가 존재하지 않기에 첫 번째 쿼리처럼 nvl함수를 사용해도 값이 나오지 않습니다.

그래서 max() 또는 min() 함수를 사용해서 NULL을 리턴 받은 다음 nvl을 사용하면

'no data'가 표출됨을 확인할 수 있습니다. 

이렇게 NULL에 대한 함수를 알아봤습니다.

수정이 필요하거나 잘못된 내용이 있으면 댓글 주세요.

아래 하트(공감) 버튼을 눌러서 더 다양한 글을 쓸 수 있게 응원 부탁드립니다. 감사합니다.

안녕하세요. 신기한 연구소입니다.

SQL작업을 하면서 유용하게 활용할 수 있는 내장 함수들이 있는데요.

문자형, 숫자형, 날짜형 등 몇 가지로 구분할 수 있는데요.

이번 포스팅은 문자형에 대해 살펴보겠습니다.

프로그램 개발을 하면 당연하게도 SQL 구문도 작성해야 합니다.

그럴 때 내장 함수를 잘 사용하면 편하게 코딩할 수 있답니다.

그럼 출발해봅니다.

SUBSTR(문자열, 시작 위치, 길이) 

-- 길이는 생략 가능하면 생략하면 문자열 끝까지 반환함.

예를 들어 생년월일(20081009) 중 연도만 추출하고 싶은 경우가 있을 겁니다.

SELECT SUBSTR(BIRTH_DT, 1, 4)

  FROM EMP;

>2008

정말 자주 사용하는 함수입니다.

LENGTH(문자열)

문자열의 길이를 숫자로 반환합니다.

예를 들어 이름이 4자 이상인 조건을 검색한다고 가정해봅니다.

SELECT *

  FROM EMP

WHERE LENGTH(EMP_NM) > 3;

이 함수도 정말 많이 사용됩니다.

LOWER(문자열)  -- 소문자로 변신~

UPPER(문자열)  -- 대문자로 변신~

알파벳으로 구성된 문자열에 대해서 대문자는 소문자로 소문자는 대문자로 변환해 준답니다.

이 내장 함수도 종종 사용하니 잘 살펴보세요.

SELECT UPPER('abcd') FROM DUAL;

>ABCD

TRIM([leading 또는 trailing 또는 both] 제거할문자 FROM 문자열) 

--문자열만 넣으면 기본 공백을 양쪽에서 제거함.

LTRIM(문자열, 제거할문자) 

--제거할문자를 생략하면 자동으로 공백 제거

RTRIM(문자열, 제거할문자) 

--제거할문자를 생략하면 자동으로 공백 제거

제거할문자를 지정하면 TRIM의 경우 옵션에 따라 앞에서 또는 뒤에서 또는 앞뒤에서 찾아가 제거합니다.

옵션을 넣지 않으면 기본으로 BOTH가 되며 제거할문자를 지정하지 않으면

기본으로 공백을 제거하며 FROM이 필요 없습니다.

LTRIM의 경우는 왼쪽에서 제거할문자를 찾아서 삭제하는데 값을 지정하지 않으면 기본으로 공백을 제거합니다.

RTRIM의 경우는 오른쪽에서 제거할문자를 찾아서 삭제하는데 값을 지정하지 않으면 기본으로 공백을 제거합니다.

SELECT TRIM(leading 'K' FROM 'KOREA') FROM DUAL;  >OREA

SELECT LTRIM('KOREA', 'K') FROM DUAL; >OREA

SELECT RTRIM('KOREA', 'A') FROM DUAL' >KORE

TRIM 내장 함수도 종종 사용하게 되니 잘 확인하세요.

CONCAT(문자열1, 문자열2) 

-- 문자열1과 문자열2를 합침, || 와 같은 기능

사실 CONCAT보다 ||를 더 많이 사용합니다.

두 개의 문자열을 합치는 경우 사용합니다.

예를 들어 기본 주소와 상세 주소의 컬럼이 분리되어 있다면

화면에 주소 한 줄로 표기해야 하는 경우가 있습니다.

SELECT MAIN_ADDR || ' ' || DETAIL_ADDR AS ADDR 

FROM CUSTOMER;

MAIN_ADDR과 DETAIL_ADDR을 결합해서 하나의 주소로 표시합니다. 

중간에 띄어쓰기를 위해 ' '을 추가했습니다.

그럼 CONCAT는 사용할 일이 없을까요?

고객 명단을 출력하는 경우 이름에는 단순하게 고객명만 표시됩니다.

그렇지만 출력하는 경우 홍길동 고객님이라고 고객님을 붙여야 하는 경우가 있을 수 있어요.

이런 경우 프로그램에서 붙여도 되고 SQL SELECT문에서 붙여도 됩니다.

보통 SQL문에서 대부분 처리합니다.

SELECT CONCAT(CUST_NM , ' 고객님') AS NM 

FROM CUSTOMER;

사실 ||를 사용하되 되지만 CONCAT를 사용해서 더 명시적으로 표시할 수 있다고 생각합니다.

CHR(ASCII)

--ASCII 값을 해당 문자나 숫자로 변환

ASCII(문자 또는 숫자) 

 --문자 또는 숫자를 ASCII로 변환

프로젝트하면서 아주 가끔 사용했던 함수입니다.

알아두면 편해요.

이번 포스팅은 개발 시 SQL문을 작성할 때 문자열 관련 내장 함수의 기능에 대해

간단하게 살펴봤습니다.

자주 사용하면 외워서 사용하겠지만 가끔 생각 안나는 경우도 있지요? ㅎㅎ

잘 활용하시기 바랍니다.

잘못된 정보나 수정이 필요하면 댓글 주세요.

감사합니다.