SQL-6) 함수(function) - 2

안녕하세요 이승혁입니다.

 

지난 실습에서 sql developer를 설치해 보다 간편하게 실습을 진행할 수 있게 되었습니다.

이번 시간은 단일행 함수 중 문자 함수, 숫자 함수, 날짜 함수 , 변환 함수에 대해 실습을 진행해 보도록 하겠습니다.

 

 

지난 시간에 했던 문자함수로는 소문자를 대문자로 변경해주는 upper , 대문자를 소문자로 변경해주는 lower,

문자의 첫 글자만 대문자로 바꾸어주는 initcap , 문자의 일부분을 잘라내는 substr, 문자의 위치를 알려주는 instr 함수를 사용해 보았습니다.

 

오늘은 문자함수 length, lpad&rpad, rtrim&trim, replace, concat

          숫자 함수 round, trunc, mod, power에 대한 실습을 진행해 보도록 하겠습니다.

---

ㆍ 단일행 함수 - 문자함수 length

 

     문자의 길이를 출력하는 함수입니다.

select ename,length(ename)
from emp;

실습 1. lenth 함수와 order 절 사용해보기, 이메일 길이가 긴 순서대로 정렬해 출력해보기

select ename,email, length(email) as len
from emp11
order by len desc;

 

※ select 절의 칼럼별칭 len을 order절에서 사용할 수 있는 이유 

 

                테이블 -> 조건 -> 칼럼 -> 정렬

 

오라클 내부 순서 from -> where -> select -> order 덕분에 select 절에서 사용한 칼럼 별칭을 order 절에서 사용할 수 있다.

---

ㆍ 단일행 함수 - 문자함수 lpad&rpad

 

     막대 그래프처럼 데이터를 간단하게 시각화할 수 있는 함수로 특정 칸의 개수를 정하고 빈칸을 채울 수 있다.

     pad(사용할 데이터, 칸 개수, 채울 문자) 형식으로 사용합니다.

 

     lpad : 좌측을 채운다.

     rpad : 우측을 채운다.

 

     이해하기 가장 쉬운 방법은 실습입니다.

 

실습 2. rpad 사용해보기, 월급 칸을 10으로 잡고 남는 칸을 우측 기준으로 별모양(*)으로 채워 출력해보기

select ename,rpad(sal,10,'*') as salary 
from emp; 

sal(SALARY) 칼럼의 총 칸을 10칸으로 지정했습니다.

rpad 함수를 사용했기 때문에 우측을 빈칸으로 인식하여 채웁니다.

1번 행의 경우 5000이라는 값은 4칸을 사용 따라서 지정한 10칸 중 나머지 6칸을 *모양으로 우측으로 채웁니다.

 

위 함수를 사용하면 보다 쉽게 크기 비교를 할 수 있는데 다음과 같은 실습을 통해 확인해보겠습니다.

 

실습 3.

1. 월급이 높은 사람부터 낮은 사람 순서로 정렬하여 출력

2. 막대 그래프 형식으로 한눈에 확인할 수 있도록 출력

 

 

보다 깔끔하고 숫자로 나타낸 것보다 보기 편하게 나타낼 수 있습니다.

코드는 다음과 같습니다.

select ename, lpad('■',sal/100,'■') as salary
from emp
order by salary desc;

 

pad함수의 변수 사용은 pad(사용할 데이터, 칸 개수, 채울 문자)라고 했습니다.

 

첫 행 KING을 예시로 설명하겠습니다.

우선 사용할 데이터는 첫 실습처럼 해당 직원의 월급의 숫자가 아닌, 막대그래프 이므로 '■' 문자를 사용했습니다.

 

칸의 개수는 해당 직원의 월급을 특정 숫자(예시의 100)로 나누어 지정합니다.

위 과정에서 각 직원들의 월급이 전체 칸의 개수로 나타내어집니다. ( 5000인 직원은 50칸, 3000인 직원은 30칸 )

 

이제 채우는 일만 남았는데 사용할 데이터는 '■' 한 개로 지정해 두었습니다. 따라서 한 개의 네모를 제외한 나머지를 모두 '■' 문자로 채우게 됩니다.

 

다음 예시를 보면 조금 더 쉽게 이해할 수 있습니다.

채울 문자를 빈 네모로 바꾸어 보았습니다. 

첫번째 변수인 '■' 하나를 가장 우측에 채우고 나머지 좌측을 '□' 문자로 채우는 것을 확인했습니다.

 

pad 함수의 변수와 작동에 대한 원리를 이해하고 정리할 수 있었습니다.

 

ㆍ 단일행 함수 - 문자 함수 rtrim&trim&ltrim

 

     trim 함수는 특정 철자 하나를 잘라낼 때 혹은 좌우 공백을 제거할 때 사용합니다.

     trim('문자' from column명) , 문자를 쓰지 않은 경우 공백만 제거합니다.

 

     여러개의 문자를 잘라낼 때 또는 좌 or 우 공백 제거할 때는 ltrim o rltrim을 사용합니다

     ltrim or rtrim(column명,'문자''문자') : 여러 개를 자를 때는 쉼표 없이 이어서 사용합니다.

 

 

select ename, trim('S' from ename)
from emp;

실습 4. 이메일의 .com, .net 지워보기

select eamil,rtrim(eamil,'.com''.net') eamil
from emp11;

rtrim 함수를 사용해 우측부터 확인해 '.com' or '.net'이 아닌 문자를 만날 때까지 제거합니다.

trim과 마찬가지로 문자를 적지 않는 경우 rtrim은 우측공백을, ltrim은 좌측 공백만을 제거합니다.

ㆍ 단일행 함수 - 문자함수 replace

     특정 문자를 다른 문자로 대체하는 함수

     replace(데이터,바꿀문자,대체문자) 의 형식을 사용합니다.

select ename, replace(sal, 0, '*')
from emp;

 

실습 5. 병원에서 사용하는 형태로 이름을 나타내보기, ex ) 이*혁

select replace(ename,substr(ename,-2,1),'*')
from emp11;

replace의 대체할 문자는 사람마다 이름이 다르기 때문에 특정 문자를 사용할 수 없습니다.

replace의 가운데 문자를 각 이름의 가운데 글자로 하기 위해 substr 함수로 이름의 가운데를 잘라냈습니다.

 

substr 함수의 문자위치 -2를 사용한 이유는 이름이 4글자인 사람들도 고려해야 하기 때문입니다.    

-2를 사용함으로써 뒤에서부터 2번째 글자를 substr의 해당 문자로 사용할 수 있습니다.

ㆍ 단일행 함수 - 문자함수 concat

 

       두 개의 컬럼의 데이터를 붙여서 출력하는 함수

 

실습 6. concat 사용해보기, 이름과 월급을 바로 붙여서 출력해보기

select concat(ename,sal)
from emp;

concat 함수 사용

 

연결 연산자 || 를 사용해도 결과는 같습니다.

연결연산자 사용

ㆍ 단일행 함수 - 숫자 함수 round

 

     숫자를 반올림 해주는 함수입니다.

     round(숫자 데이터, 반올림 위치)

     반올림 위치는 1의 자리를 0을 기준으로 소수점 첫 번째 자리는 1 , 두 번째 자리는 2 순서로 사용하며

     십의 자리는 반대로 -1을 사용합니다.

 

select '787.567', round( 787.567, -1) , -- 790
                  round( 787.567, 0) ,  -- 788
                  round( 787.567, 1) ,  -- 787.6
                  round( 787.567, 2)    -- 787.57
from dual;

ㆍ 단일행 함수 - 숫자 함수 trunc

 

    숫자를 버림 하는 함수입니다.

    버림의 위치는 1의 자리를 0을 기준으로 소수점 첫 번째 자리는 1 , 두 번째 자리는 2 순서로 사용하며

    십의 자리는 반대로 -1을 사용합니다.

select '787.567', trunc( 787.567, -1) , -- 780
                  trunc( 787.567, 0) ,  -- 787
                  trunc( 787.567, 1) ,  -- 787.5
                  trunc( 787.567, 2)    -- 787.56
from dual;

 

ㆍ 단일행 함수 - 숫자 함수 mod

     나머지 값을 출력하는 함수입니다.

     mod(숫자 데이터, 나눌숫자) 형태로 사용합니다

select mod(10,3)
from dual;

실습 7. 나이가 짝수면 0 홀수면 1 출력해보기

select age,mod(age,2) as 짝홀수판별
from emp11;

 

이번 포스팅은 여기서 마치고 다음 포스팅에서는 날짜를 다루는 날짜 함수, 데이터의 유형 자체를 변경하는 변환 하는

변환 함수를 사용하여 SQL에서 날짜를 다룰 줄 알고, 데이터의 유형에 익숙해지는 시간을 갖도록 하겠습니다.

 

읽어주셔서 감사합니다